Help with syntax

[jkfeagle]

Can someone tell me what's wrong with the sytax in this statement?

dbsPLO_Load.Execute "UPDATE (ztblCCCFinal) SET (OCY = dimlisvar) WHERE (matchvar = matevar)"

I keep getting the error "Syntax error in UPDATE statement" no matter what I seem to try! Thanks!

 

[raskew]

Probably the easiest way to fix it is to place your update statement in a new query and sort it out from the query grid.
Here's an example of one that does work, and you can see some differences in syntax:

UPDATE tblEmpTimesNew SET tblEmpTimesNew.StartTime = [endtime]
WHERE (((tblEmpTimesNew.EmpID)="A0002"));

 

[jkfeagle]

The problem with that is that the values in this line of code are from a larger module that loops through a recordset. They are variables so I don't think that I can just set it up in a query window.

 

[Jack Cowley]

See if this works for you:

dbsPLO_Load.Execute "UPDATE ztblCCCFinal SET OCY = " & dimlisvar & " WHERE matchvar = " & matevar & ""

 

[jkfeagle]

Jack,

No luck with that attempt. There are actually three variables in the line: dimlisvar, matchvar and matevar. Does that change anything?

 

[jkfeagle]

Jack,

Here's something that might be of help. When I change the syntax to:

dbsPLO_Load.Execute "UPDATE ztblCCCFinal SET OCY = " & dimlistvar & " WHERE " & matchvar = matevar & ""

The right values appear to be in the variables and I'm not getting a syntax error. Instead the following error appears:

The Microsoft Jet database engine cannot find the input table or query 'False'. Make sure that it exists and that its name is spelled correctly.

Now the variables matchvar and matevar are not equal at this point so I suspect it has something to do with that. I do not call any table or query called 'False' in my code. Any ideas?

 

[Jack Cowley]

CurrentDb.Execute "UPDATE ztblCCCFinal SET OCY = '" & dimlistvar & "' WHERE [" & matchvar & "] = '" & matevar & "'"

This works for me where dimlistvar, matchvar and matevar are strings.

hth,
Jack

 

[jkfeagle]

Thanks Jack!

You got me pretty close. The one that ended up working was actually:

CurrentDb.Execute "UPDATE ztblCCCFinal SET OCY = '" & dimlistvar & "' WHERE '" & matchvar & "' = '" & matevar & "'"

I appreciate you taking the time to help me with this!

 

[Jack Cowley]

I am glad I was able to assist. Continued success with Access!

Jack