Dlookup Function giving error in SQL String

[Edward S]

Friends, I had posted this on "Microsoft.Public.Access", But I did not
get a satisfactory response. I have been struggling for nearly 2 days
trying to crack this code. I really need help, could some one please
help me......
The Problem....

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]","T
blCurStore","[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]","T
blCurStore","[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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom van Stiphout]

On 18 May 2004 04:16:35 GMT, Edward S <so******@devdex.com> wrote:

The "expected: end of statement" error can be debugged by setting a
breakpoint on that line, and in the Immediate window type:
?strSql
and paste taht string into a new query.
The query parser will show you where the problem is. Typically a
mismatch in parentheses.

-Tom.

Friends, I had posted this on "Microsoft.Public.Access", But I did not
get a satisfactory response. I have been struggling for nearly 2 days
trying to crack this code. I really need help, could some one please
help me......
The Problem....

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]","T
blCurStore","[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]","T
blCurStore","[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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!