Help! When running SQL statement string treated as expression - whatis the cure?

[plh]

Hello Gurus,
Here is my routine:

Private Sub cmdAddPNum_Click()
Dim strSQL As String
Dim strPNum As String
strPNum = Me.ProblemNumber.Value

strSQL = "UPDATE Items SET Items.[Problem Number] = " & "" & strPNum &
"" & _
" WHERE (((Items.[Audit Serial Number])=" &
Me.Audit_Serial_Number.Value & "));"
Debug.Print strSQL
DoCmd.RunSQL (strSQL)
End Sub

In the Immediate window strSQL shows as

UPDATE Items SET Items.[Problem Number] = 774QM-1-2.2 WHERE (((Items.
[Audit Serial Number])=774));

and DoCmd returns error message:
#3075 Syntax error (missing operator) in query expression
'774QM-1-2.2'.

The double quotes between the two ampersands were my latest effort to
get it to put 774QM-1-2.2 into quotation marks so that it would stop
trying to parse it as an expression, but that did not work either.
What to do?
Thank You,
-plh

 

[XPS350]

Hello Gurus,
Here is my routine:

Private Sub cmdAddPNum_Click()
Dim strSQL As String
Dim strPNum As String
strPNum = Me.ProblemNumber.Value

strSQL = "UPDATE Items SET Items.[Problem Number] = " & "" & strPNum &
"" & _
" WHERE (((Items.[Audit Serial Number])=" &
Me.Audit_Serial_Number.Value & "));"
Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
End Sub

In the Immediate window strSQL shows as

UPDATE Items SET Items.[Problem Number] = 774QM-1-2.2 WHERE (((Items.
[Audit Serial Number])=774));

and DoCmd returns error message:
#3075 Syntax error (missing operator) in query expression
'774QM-1-2.2'.

The double quotes between the two ampersands were my latest effort to
get it to put 774QM-1-2.2 into quotation marks so that it would stop
trying to parse it as an expression, but that did not work either.
What to do?
Thank You,
-plh

Try:
strSQL = "UPDATE Items SET Items.[Problem Number]=’" & strPNum &
"’" & _


Groeten,

Peter
http://access.xps350.com

 

[Marshall Barton]

Private Sub cmdAddPNum_Click()
Dim strSQL As String
Dim strPNum As String
strPNum = Me.ProblemNumber.Value

strSQL = "UPDATE Items SET Items.[Problem Number] = " & "" & strPNum &
"" & _
" WHERE (((Items.[Audit Serial Number])=" &
Me.Audit_Serial_Number.Value & "));"
Debug.Print strSQL
DoCmd.RunSQL (strSQL)
End Sub

In the Immediate window strSQL shows as

UPDATE Items SET Items.[Problem Number] = 774QM-1-2.2 WHERE (((Items.
[Audit Serial Number])=774));

and DoCmd returns error message:
#3075 Syntax error (missing operator) in query expression
'774QM-1-2.2'.

The double quotes between the two ampersands were my latest effort to
get it to put 774QM-1-2.2 into quotation marks so that it would stop
trying to parse it as an expression, but that did not work either.

strSQL = "UPDATE Items SET Items.[Problem Number] = """ &
strPNum &"""" & _
" WHERE Items.[Audit Serial Number]=" &
Me.Audit_Serial_Number

The quotes were wrong, but I also removed a bunch of
unecessary stuff just to make it easier to read.
Are you sure the Audit Serial Number field is bount to the
Audit_Serial_Number text box? The names are different in an
unusual way and, even if they are both correct, I think this
kind of naming is another example of why you should never
use non alphanumeric characters in any name.

 

[plh]

Private Sub cmdAddPNum_Click()
Dim strSQL As String
Dim strPNum As String
strPNum = Me.ProblemNumber.Value

strSQL = "UPDATE Items SET Items.[Problem Number] = " & "" & strPNum&
"" & _
" WHERE (((Items.[Audit Serial Number])=" &
Me.Audit_Serial_Number.Value & "));"
Debug.Print strSQL
   DoCmd.RunSQL (strSQL)
End Sub

In the Immediate window strSQL shows as

UPDATE Items SET Items.[Problem Number] = 774QM-1-2.2 WHERE (((Items.
[Audit Serial Number])=774));

and DoCmd returns error message:
#3075 Syntax error (missing operator) in query expression
'774QM-1-2.2'.

The double quotes between the two ampersands were my latest effort to
get it to put 774QM-1-2.2 into quotation marks so that it would stop
trying to parse it as an expression, but that did not work either.

strSQL = "UPDATE Items SET Items.[Problem Number] = """ &
strPNum &"""" & _
"  WHERE Items.[Audit Serial Number]=" &
Me.Audit_Serial_Number

The quotes were wrong, but I also removed a bunch of
unecessary stuff just to make it easier to read.
Are you sure the Audit Serial Number field is bount to the
Audit_Serial_Number text box?  The names are different in an
unusual way and, even if they are both correct, I think this
kind of naming is another example of why you should never
use non alphanumeric characters in any name.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you. I was helping someone else with theirs. It was solved but
in a different way. I did notice that funny naming, I always use &
promote stuff like txtAuditSerialNumber but who listens?
-plh