RecordSet in Access

Code:

---

Function Postage_Charge(Postage As String, Country As String) As Currency

    Set RecordD = New ADODB.Recordset
    RecordD.Open "SELECT Postage, Country FROM tblPostal", CurrentProject.Connection

    'Return Postal charges plus shipping'
    Postage_Charge = RecordD.Fields(0).Value + RecordD.Fields(1).Value

End Function

---

The problem I have is I want to pass into RecordD.Open the value of Postage, which may be "First" for example and not the string "Postage" as it thinks now. Eg:

Code:

---

Something = Postage_Charge("First", "UK");

---

(both First and UK exist in a table)

I have tried $Postage with no luck. Anyone have any suggestions?

Thanks:thumb:

At the moment, the value of "postage" which you've got inside of the argument to the Open method is being treated as part of a string as it's enclosed (along with the rest of the SQL command) in double quotes.

Try changing the code to this & the postage value which makes up the SQL command should equal whatever value you pass into the function/ the value of the postage variable instead:

VB Code:

Function Postage_Charge(Postage As String, Country As String) As Currency
 
    Set RecordD = New ADODB.Recordset
    RecordD.Open "SELECT [b]" & Postage & ", " & Country  & _
    "[/b] FROM tblPostal", CurrentProject.Connection
 
    'Return Postal charges plus shipping'
    Postage_Charge = RecordD.Fields(0).Value + RecordD.Fields(1).Value
 
End Function

Thanks alot. I will try this and let you know how it went.

Don't you need to filter the amounts?

IE with a where clause?
Building on Alex Reads post:

VB Code:

Function Postage_Charge(Postage As String, Country As String) As Currency
    dim strSql as string
 
    strsql="SELECT Postage, Country "
    strsql=strsql & "FROM tblPostal "
    strsql=strsql & "WHERE [postage]='" & Postage & "' and [country]='" & Country  & "'"
 
    Set RecordD = New ADODB.Recordset
    RecordD.Open strsql,CurrentProject.Connection,3,3,1 'static, optimistic, adcmdtext
 
    'Return Postal charges plus shipping'
    if recordd.eof then
'---- no records returned
    else
'---- check your fields...?
       Postage_Charge = RecordD.Fields(0).Value + RecordD.Fields
(1).Value
    end if
 
'---- close recordset and clean up
    recordd.close
    set recordd = nothing
End Function

Thanks, I have used both of the ideas. Cheers!