Dlookup Function giving error in SQL String

[Edward S]

Friends, I had posted this on "Microsoft.Publ ic.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 (OriginalCurrAm t) less the Disputed
Amount (DisputedAmtOCu r) both from the table "TblInvoiceMain " and then
multiplied with Currency Rate (Rate) that is stored in the table
"TblCurStor e", if the criteria matches the Currency (ShrtCur) {example
-'USD'} and the Period in the "TblCurStor e" to the field
"OriginalCu rr" and "FinRecdDat e" 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.De lete ("QryFrmReport" )

Set qdf3 = db.CreateQueryD ef("QryFrmRepor t", StrSQL)
RefreshDatabase Window
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******@devde x.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.Publ ic.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 (OriginalCurrAm t) less the Disputed
Amount (DisputedAmtOCu r) 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 "TblCurStor e" to the field
"OriginalCur r" and "FinRecdDat e" 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 .OriginalCurrAm t, 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.D elete ("QryFrmReport" )

Set qdf3 = db.CreateQueryD ef("QryFrmRepor t", StrSQL)
RefreshDatabas eWindow
End of Code:

Thanks

Edward
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!