syntax error

  • Thread starter Thread starter cporter
  • Start date Start date
C

cporter

I'm getting a compile/syntax error message on the string below. I don't
see it. What do I need to change?

StrSQL = UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID =
tblFPMTemp.MACHID SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue
WHERE (((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));
 
The purpose in populating a string variable with an SQL statement is to pass
that statement to something that will execute it. In this case you'll
probably be using the db.Execute strSQL construct.

In order to do so, you must populate strSQL with a string value, which your
code doesn't.

The following example does that, but it assumes vValue is a numeric value.

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp " & _
"ON FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & vValue
& _
" WHERE
(((tblFPMTemp.MACHID)=[Forms]![formFPMByNum]![MACHID]));"

If vValue is also a string value, then you need to enclose it in single or
double quotes.
"SET tblFPMTemp.EFLH = '" & vValue & "', ...
or
"SET tblFPMTemp.EFLH = """ & vValue & """, ...

The first example, uses a single quote ( ' ). The second uses double quotes
( " ). Because double-quotes are a special character, you must use it twice,
so Access recognises the fact that you want to include a quote, rather than
closing a sting value; hence three quotes together.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
When you set the value of a string variable, you need to place quotes around
the string.

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]

This assumes MACHID is numeric. If it is text, try:

StrSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= """ & _
[Forms]![formFPMByNum]![MACHID] & """"
 
OK changed the code and Iam getting a new error message, "to few
parameters" and the code phrase, CurrentDb().Execute strSQL,
dbFailOnError, is highlighted.

The whole code is below:


Private Sub TimeUp_AfterUpdate()
Dim vValue As Variant
Dim strSQL As String


If Forms!formFPMByNum!MACHID = "chi-002" Then
'Get the value from a control on a form or ask the user for the value
vValue = InputBox("What EFLH", "EFLH", 10)
ElseIf Forms!formFPMByNum!MACHID = "car-001" Then
vValue = InputBox("What EFLH", "EFLH", 10)
ElseIf Forms!formFPMByNum!MACHID = "car-002" Then
vValue = InputBox("What EFLH", "EFLH", 10)

'Or you can use
'vValue = Forms!formFPMByNum!EFLH

Else
vValue = 0
End If

If IsNumeric(vValue) = True Then
strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = vValue, FPM.EFLH = vValue " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]

CurrentDb().Execute strSQL, dbFailOnError
Else
MsgBox "Invalid EFLH value"
End If
End Sub
 
The query engine won't know what vValue is.
Try:
strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
 
It doesn't run. The thing pops up a msb box saying compile error:
syntax error and stops with the code highlighted. If the debug.print
is supposed to do something I don't see it anywhere.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
 
You missed my mistake ;-) There was an ampersand missing following the
second vValue.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]


--
Duane Hookom
MS Access MVP


It doesn't run. The thing pops up a msb box saying compile error:
syntax error and stops with the code highlighted. If the debug.print
is supposed to do something I don't see it anywhere.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
Click to expand...
 
Duane,

Thanks for hanging with me.

The new error is run time error '3061':
to few parameters. Expected 1

The print StrSQL reads:
"UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = 19500, FPM.EFLH = 19500 WHERE tblFPMTemp.MACHID=
CHI-002"

Inserting this into the SQL window of a query and running it generated
a dialog box asking for the value of CHI. Could the hyphen in the
MachID be causing a problem? I enclosed CHI-002 in double quotes and
the update query ran ok.


It looks like I am going to be writing a lot more code in VBA. To do
this well I'm going to need some references and something to walk me
through the basics. What would you suggest?
 
Note my first reply had two different suggestions based onif MACHID was
numeric or text. You chose the numeric solution while your MACHID is
apparently text. Try:

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= """ & _
[Forms]![formFPMByNum]![MACHID] & """"

--
Duane Hookom
MS Access MVP


Duane,

Thanks for hanging with me.

The new error is run time error '3061':
to few parameters. Expected 1

The print StrSQL reads:
"UPDATE FPM INNER JOIN tblFPMTemp ON FPM.MACHID = tblFPMTemp.MACHID SET
tblFPMTemp.EFLH = 19500, FPM.EFLH = 19500 WHERE tblFPMTemp.MACHID=
CHI-002"

Inserting this into the SQL window of a query and running it generated
a dialog box asking for the value of CHI. Could the hyphen in the
MachID be causing a problem? I enclosed CHI-002 in double quotes and
the update query ran ok.


It looks like I am going to be writing a lot more code in VBA. To do
this well I'm going to need some references and something to walk me
through the basics. What would you suggest?



Duane said:
You missed my mistake ;-) There was an ampersand missing following the
second vValue.

strSQL = "UPDATE FPM INNER JOIN tblFPMTemp ON " & _
"FPM.MACHID = tblFPMTemp.MACHID " & _
"SET tblFPMTemp.EFLH = " & vValue & ", FPM.EFLH = " & _
vValue & " " & _
"WHERE tblFPMTemp.MACHID= " & _
[Forms]![formFPMByNum]![MACHID]
Click to expand...
Click to expand...
 
Duane,

It's working now, thank you. I had thought you meant the value of
vValue was numeric or text.

Carl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update query conditional data 7
Update query issue 0
syntax error with multiple inner join query - Access 2003 8
SQL VBA -- INNER JOIN Syntax 6
SQL Syntax Error - UPDATE command 1
can't determine the syntax error 1
Syntax Error while running a stored procedue 6
Syntax error in FROM clause 3

Back
Top