asked on

Access VBA SQL Syntax

Have a syntax error in this Update query. Make query in query designer and pasted it into module with line continuation characters " & _ What am I doing wrong?

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
"DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
"and DetailID= [Forms]!frmRateTool.[DetailID]") " & _
"WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));"

Rey Obrero (Capricorn1)

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
"DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
"and DetailID= [Forms]!frmRateTool.[DetailID]") " & _
" WHERE (tblRateDetails.DetailID)= " & [Forms]![frmRateTool]![DetailID]

Simon

If in doubt, take the line continuation characters out and check that it runs as a single line.

ExpressMan1

ASKER

Tried single line and Rey's code, get syntax error on both.

Here is the sql from the query designer which works.

UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = DSum("Charge","tblRateCharges","FuelChargeable= -1 and DetailID= [Forms]!frmRateTool.[DetailID]")
WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));

ASKER CERTIFIED SOLUTION

Robert Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

Is the form open?

Depending on the circumstances, you might try this version of the SQL, which concatenates the form control value during string construction.

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = " & _
                "DSum("Charge","tblRateCharges","FuelChargeable= -1 " & _
                "and DetailID=" & [Forms]!frmRateTool.[DetailID] & ") " & _
                "WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool]![DetailID]));"

Open in new window

Rey Obrero (Capricorn1)

sorry, didn't see the Dsum, try this copy and paste

strSQL = "UPDATE tblRateDetails SET tblRateDetails.AddlFuelChargeable = DSum('Charge','tblRateCharges', 'FuelChargeable= -1" _
& " and DetailID= " & [Forms]!frmRateTool.[DetailID] & ")" _
& " WHERE (tblRateDetails.DetailID)= " & [Forms]![frmRateTool]![DetailID]

Robert Sherman

Essentially, the problem has to do with the quotation marks inside the string... I think I fixed all of them, but if you run into any issues with the version I just posted, look for any other instances where there is a quotation mark inside the string that I may have missed.... double quotation marks right next to each other take care of this problem by telling the compiler that you actually want a quotation mark and not that you are signalling the end of the string.

ExpressMan1

ASKER

Works !! Extra quotation marks, now I know.

Thanks to all