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

  • Thread starter Thread starter Edward S
  • Start date Start date
E

Edward S

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
 
StrSQL = StrSQL &
"Round(CDbl(nz(([OriginalCurrAmt]-[DisputedAmtOCur])*DLookUp("[Rate]","TblCu
rStore","[ShrtCur]='"
& [OriginalCurr] & "' AND [Period] = '" &
Format([FinRecdDate],"mmm/yy") & "'"),0)),2) AS USDollarAmt "
Click to expand...


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 said:
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]","TblCu
rStore","[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]","TblCu
rStore","[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
Click to expand...
 
Back
Top