Function To Return Result of a Query (1 Viewer)

[AJR]

Hi

I'll try and explain this in a simplistic way as I tend to overcomplicate my questions. I think my problem will be revealed quite readily by the code.

I am trying to assign the result of a query to a variable so I can use it in a formula. From what I can gather this is possible by way of a Custom Function or Assigning the SQL result directly. I am posting each as a separate thread.

This question relates to the function.

I am trying to modify some code that was suggested in an earlier question. That question was resolved in a different manner, so this is the first time I have tried to do this.

Here is the code

Public Function Xrate(ByVal dtTransDate As Date, ByVal strCurrency As String) As Single
   'Declare Variables
    Dim strSQL As String
    Dim db As DAO.Database
   'Define Variables
    Set db = CurrentDb
    strSQL = "SELECT tblFXRates.fxXRate FROM tblFXRates  WHERE tblFXRates.[fxDate] = #" & Format(dtTransDate, "mm/dd/yyyy") & "# AND " & _
             "tblFXRates.[fxCurrency] = '" & strCurrency & "';"
    
    Xrate = NZ(db.OpenRecordSet(strSQL)(0),(0)

    Set db = Nothing
 End Function

To test the function I am entering, in the Immediate Window, both:

?XRate(12/18/2015,"USD") and
?XRate(12/18/2015,USD)

Both times I get a "Compile Error: Syntax Error" indicated on the line:

Xrate = NZ(db.OpenRecordSet(strSQL)(0),(0)

I suspect that the root of the error is in the SQL but I have quadruple checked and it is copied exactly as written.

Can someone point out where I am going wrong?

Thank you very much for your time

A/R

 

[Guus2005]

I understand your wish to create oneliners but in this case you should rewrite your code into a more readable one.

Public Function Xrate(ByVal dtTransDate As Date, ByVal strCurrency As String) As Single
   'Declare Variables
    Dim strSQL As String
    Dim db As DAO.Database
    dim rs as recordset

   'Define Variables
    Set db = CurrentDb
    strSQL = "SELECT tblFXRates.fxXRate FROM tblFXRates  WHERE tblFXRates.[fxDate] = #" & Format(dtTransDate, "mm/dd/yyyy") & "# AND " & _
             "tblFXRates.[fxCurrency] = '" & strCurrency & "';"
    
    set rs = db.OpenRecordSet(strSQL)

'    Xrate = Nz(rs.fields(0),(0)
    Xrate = Nz(rs.fields(0),0)

    Set db = Nothing
 End Function

Or you can try this:
Xrate = Nz(db.OpenRecordSet(strSQL)(0),0)

As you can see, the first one is better readable. If you did write it like that, you could see that your SQL was not the problem.

HTH

 

[arnelgp]

the problem is here:

Xrate = NZ(db.OpenRecordSet(strSQL)(0),(0)

you shoud probably use the DLookup function, instead of creating all those object

XRate = NZ(DLookUp("fxXRate","tblFXRates","[fxDate] = #" & Format(dtTransDate, "mm/dd/yyyy") & "# AND " & [fxCurrency] = '" & strCurrency & "'"), 0)

 

[AJR]

Guus2005

Thanks for the comment

I don't actually know what a "one liner" is and I'm not sure what "The first one" refers to. Forgive my inexperience. I noted that I included one line twice (commented). That was a leftover from an attempt to get it to work. I will try your suggestion and see if it works though.

Thanks

A/R

 

[Guus2005]

the problem is here:

Xrate = NZ(db.OpenRecordSet(strSQL)(0),(0)

you shoud probably use the DLookup function, instead of creating all those object

XRate = NZ(DLookUp("fxXRate","tblFXRates","[fxDate] = #" & Format(dtTransDate, "mm/dd/yyyy") & "# AND " & [fxCurrency] = '" & strCurrency & "'"), 0)

Hi arnelgp,

FYI: creating all those objects are faster than the execution of the domain functions. Please refer to http://www.access-programmers.co.uk/forums/showpost.php?p=1068234&postcount=2

Share & Enjoy!

 

[AJR]

Guus2005

That worked quite nicely. Thanks Again

A/R