Syntax error in SQL - please help!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I'm trying to run the following update sql from a vb module and it tells
me there is a syntax error. I just can't see it. Is it really obvious and
where is it?

MySql = "UPDATE [t-CommentOutput] SET [t-CommentOutput].Country = " &
MyCountry & " " & _
"WHERE ((([t-CommentOutput].Country) Is Null));"
 
If Country is a text field then country should have quotes around it, the
syntax should be.

MySQL = "UPDATE [t-CommentOutput] SET [t-CommentOutput].Country='" &
MyCountry & "' WHERE [t-CommentOutput].Country Is Null;"

Note the single quotes ' after the equals and before the WHERE.

Hope this helps.
 
What data type is Country: text or numeric?

If it's text, you need quotes around the value you're trying to set it to:

MySql = "UPDATE [t-CommentOutput] " & _
"SET [t-CommentOutput].Country = '" & _
MyCountry & "'" & _
"WHERE ((([t-CommentOutput].Country) Is Null));"

Exagerated for clarity, that's


MySql = "UPDATE [t-CommentOutput] " & _
"SET [t-CommentOutput].Country = ' " & _
MyCountry & " ' " & _
"WHERE ((([t-CommentOutput].Country) Is Null));"


On the other hand, are you sure you have a value for MyCountry?
 
Andrew thanks a lot - much appreciated!
--
Sharon


Andrew Tapp said:
If Country is a text field then country should have quotes around it, the
syntax should be.

MySQL = "UPDATE [t-CommentOutput] SET [t-CommentOutput].Country='" &
MyCountry & "' WHERE [t-CommentOutput].Country Is Null;"

Note the single quotes ' after the equals and before the WHERE.

Hope this helps.

Sharon said:
Hi I'm trying to run the following update sql from a vb module and it tells
me there is a syntax error. I just can't see it. Is it really obvious and
where is it?

MySql = "UPDATE [t-CommentOutput] SET [t-CommentOutput].Country = " &
MyCountry & " " & _
"WHERE ((([t-CommentOutput].Country) Is Null));"
Click to expand...
Click to expand...
 
Thanks Douglas.
--
Sharon


Douglas J Steele said:
What data type is Country: text or numeric?

If it's text, you need quotes around the value you're trying to set it to:

MySql = "UPDATE [t-CommentOutput] " & _
"SET [t-CommentOutput].Country = '" & _
MyCountry & "'" & _
"WHERE ((([t-CommentOutput].Country) Is Null));"

Exagerated for clarity, that's


MySql = "UPDATE [t-CommentOutput] " & _
"SET [t-CommentOutput].Country = ' " & _
MyCountry & " ' " & _
"WHERE ((([t-CommentOutput].Country) Is Null));"


On the other hand, are you sure you have a value for MyCountry?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sharon said:
Hi I'm trying to run the following update sql from a vb module and it tells
me there is a syntax error. I just can't see it. Is it really obvious and
where is it?

MySql = "UPDATE [t-CommentOutput] SET [t-CommentOutput].Country = " &
MyCountry & " " & _
"WHERE ((([t-CommentOutput].Country) Is Null));"
Click to expand...
Click to expand...
 
Back
Top