We help IT Professionals succeed at work.

Access VBA SQL Syntax Error in Where Clause

ExpressMan1
ExpressMan1 asked
on
I am getting a syntax error on the following:

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

The following without the WHERE clause works but returns the sum for all records instead of the current form record.  
strSQL = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & _
"ON tblRateCharges.DetailID = tblRateDetails.DetailID " & _
"SET tblRateDetails.AddlFuelChargeable = " & Nz(DSum("Charge","tblRateCharges","FuelChargeable= -1"),0)

Tried using a variable set to the DetailID on the current form in the WHERE clause
  "WHERE (((tblRateDetails.DetailID)= " & intDetailID & "));"   but still get syntax error.
Comment
Watch Question

Jeff DarlingDeveloper Analyst

Commented:
try this

Dim strSQL

strSQL = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & vbCrLf
strSQL = strSQL & "ON tblRateCharges.DetailID = tblRateDetails.DetailID " & vbCrLf
strSQL = strSQL & "SET tblRateDetails.AddlFuelChargeable = Nz(DSum(" & Chr(34) & "Charge" & Chr(34) & "," & Chr(34) & "tblRateCharges" & Chr(34) & "," & Chr(34) & "FuelChargeable= -1" & Chr(34) & "), 0)" & vbCrLf
strSQL = strSQL & "WHERE (((tblRateDetails.DetailID)=" & Chr(34) & [Forms]![frmRateTool].[DetailID] & Chr(34) & "));"

Debug.Print strSQL

Open in new window

Top Expert 2016

Commented:
change this
"WHERE (((tblRateDetails.DetailID)=[Forms]![frmRateTool].[DetailID]));"

with If DetailID is number

"WHERE (tblRateDetails.DetailID)=" & [Forms]![frmRateTool].[DetailID]

if DetailID is text

"WHERE (tblRateDetails.DetailID)='" & [Forms]![frmRateTool].[DetailID] & "'"

Author

Commented:
Jeff, Data Type mismatch using,

strSQLFuelChargeable = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & vbCrLf
strSQLFuelChargeable = strSQLFuelChargeable & "ON tblRateCharges.DetailID = tblRateDetails.DetailID " & vbCrLf
strSQLFuelChargeable = strSQLFuelChargeable & "SET tblRateDetails.AddlFuelChargeable = Nz(DSum(" & Chr(34) & "Charge" & Chr(34) & "," & Chr(34) & "tblRateCharges" & Chr(34) & "," & Chr(34) & "FuelChargeable= -1" & Chr(34) & "), 0)" & vbCrLf
strSQLFuelChargeable = strSQLFuelChargeable & "WHERE (((tblRateDetails.DetailID)=" & Chr(34) & [Forms]![frmRateTool].[DetailID] & Chr(34) & "));"

No error using,
strSQLFuelChargeable = strSQLFuelChargeable & "WHERE (((tblRateDetails.DetailID)=" & intDetailID & "));"
But the field AddlFuelChargeable not updating.

Rey,
Still get syntax error on,
strSQLFuelChargeable = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & _
"ON tblRateCharges.DetailID = tblRateDetails.DetailID " & _
"SET tblRateDetails.AddlFuelChargeable = " & Nz(DSum("Charge","tblRateCharges","FuelChargeable= -1"),0) " & _
"WHERE (tblRateDetails.DetailID)=" & [Forms]![frmRateTool].[DetailID]
Jeff DarlingDeveloper Analyst

Commented:
Maybe the SQL conditions are not being met.

Can you try querying the data manually to see if you have a detailID in both tables?

replace 123456 with the actual ID

select * from tblRateCharges 
INNER JOIN tblRateDetails 
ON tblRateCharges.DetailID = tblRateDetails.DetailID 
WHERE (((tblRateDetails.DetailID)="123456"));

Open in new window

Top Expert 2016

Commented:
try this

strSQLFuelChargeable = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & _
"ON tblRateCharges.DetailID = tblRateDetails.DetailID " & _
"SET tblRateDetails.AddlFuelChargeable = " & Nz(DSum("Charge", "tblRateCharges", "FuelChargeable= -1"), 0) & _
"WHERE (tblRateDetails.DetailID)=" & [Forms]![frmRateTool].[DetailID]

Author

Commented:
Syntax error (missing operator) in query expression '24.75WHERE
(tblRateDetails.DetailID)=2'.

24.75 is the Sum of all records "Charge" in tblCharges, instead of just the sum of the current form DetailID 2

DetailID is linked and showing 2 in both Main form and Subform. Main form is based on tblRateDetails and Subform on tblRateCharges.
Jeff DarlingDeveloper Analyst

Commented:
You need a space before the WHERE clause.

" WHERE (tblRateDetails.DetailID)=" & [Forms]![frmRateTool].[DetailID]

Open in new window

Top Expert 2016
Commented:
try this

strSQLFuelChargeable = "UPDATE tblRateCharges INNER JOIN tblRateDetails " & _
"ON tblRateCharges.DetailID = tblRateDetails.DetailID " & _
"SET tblRateDetails.AddlFuelChargeable = " & Nz(DSum("Charge", "tblRateCharges", "FuelChargeable= -1"), 0) & _
" WHERE (tblRateDetails.DetailID)=" & [Forms]![frmRateTool].[DetailID]

Author

Commented:
No error with space before the WHERE but still totaling all records instead of just the current record.

Should I be referencing the DetailID on the subform (sfrmRateAdditional) instead of tblRateDetails?

The syntax seems to be fixed just not getting the correct result.
Developer Analyst
Commented:
This needs changing.

 Nz(DSum("Charge", "tblRateCharges", "FuelChargeable= -1"), 0)

Open in new window


suspect that you want to also include DetailID on this Dsum

Author

Commented:
Thanks very much to both of you.

Going to work on the Dsum as suggested.