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

I am from the Access support department at http://www.enterpriseprojectconsulting.com and we would be happy to assist you in resolving your issue

Thank you

Enterprise Project Consulting