Dlookup function in a SQL Statement giving Error Expected: End of Statement

message from Edward S on 17 May 2004

I would appreciate if someone could help me with this particular SQL
Statement. The USDollarAmt field is a calculated field, calculated
from the Original Currency Amount (OriginalCurrAmt) less the Disputed
Amount (DisputedAmtOCur) both from the table "TblInvoiceMain" and then
multiplied with Currency Rate (Rate) that is stored in the table
"TblCurStore", if the criteria matches the Currency (ShrtCur) {example
-'USD'} and the Period in the "TblCurStore" to the field
"OriginalCurr" and "FinRecdDate" stored in the Shortdate format
{example - "21-Jan-04"} in the TblInvoiceMain. I am having problems
with this particular line below:

StrSQL = StrSQL & "Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCurStore","[ShrtCur]='"
& [OriginalCurr] & "' AND [Period] = '" &
Format([FinRecdDate],"mmm/yy") & "'"),0)),2) AS USDollarAmt "

I get an error "Expected: End of Statement".

I would be pleased if someone could help me with the syntax

The Complete code attached to an onclick event of a button on the
Report form is as follows:

Start of Code:

Dim TxtCriteria As String
Dim StrSQL As String
Dim qdf3 As QueryDef
Dim db As DAO.Database
Set db = CurrentDb

StrSQL = StrSQL & "SELECT TblInvoiceMain.SSANo,
TblInvoiceMain.ContractNo, TblInvoiceMain.FinRecdDate,
TblInvoiceMain.InvoiceNo, "
StrSQL = StrSQL & "TblInvoiceMain.OriginalCurr,
TblInvoiceMain.OriginalCurrAmt, TblInvoiceMain.DisputedAmtOCur, "
StrSQL = StrSQL & "Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCurStore","[ShrtCur]='"
& [OriginalCurr] & "' AND [Period] = '" &
Format([FinRecdDate],"mmm/yy") & "'"),0)),2) AS USDollarAmt "
StrSQL = StrSQL & "FROM TblInvoiceMain "
StrSQL = StrSQL & "ORDER BY TblInvoiceMain.ContractNo,
TblInvoiceMain.FinRecdDate; "

db.QueryDefs.Delete ("QryFrmReport")

Set qdf3 = db.CreateQueryDef("QryFrmReport", StrSQL)
RefreshDatabaseWindow

End of Code:

Thanks

Edward

Michelle replied to Edward S on 17 May 2004

"Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCu
rStore","[ShrtCur]='"

You can't have the double-quotes (") in the DLookup function in your syntax
because then Jet thinks you're ending the StrSQL string. Try changing your
double-quotes to single-quotes (if they're inside the SQL string) or 3
double-quotes will also work, but I think it's easier to read the single
quote. Use double-quotes only to begin/end the string.

"Edward S" <soaresec@qatar.net.qa> wrote in message
news:57703a81.0405170547.64d1168c@posting.google.com...
"Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCu
rStore","[ShrtCur]='"
"Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCu
rStore","[ShrtCur]='"

Archived message: Dlookup function in a SQL Statement giving Error Expected: End of Statement (Microsoft Access Forms)