By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Using DoCmd.RunSQL to Update Table

100+
P: 134
Hi, I try to run the below statement, it alway popup a box and prompt me to input the unit, what wrong with my statement. There is no sub form.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "Update tblvndprod set unit= " & Me.UnitMeasurement & " where productID=" & Me.ProductID
  2.  
Dec 18 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Please use code tags.

You need to delimit your UnitMeasurement as a string. Without quotes, it will think it's a variable.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "Update tblvndprod set unit= '" & Me.UnitMeasurement & "' where productID=" & Me.ProductID
  2.  
Dec 18 '07 #2

100+
P: 134
Please use code tags.

You need to delimit your UnitMeasurement as a string. Without quotes, it will think it's a variable.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "Update tblvndprod set unit= '" & Me.UnitMeasurement & "' where productID=" & Me.ProductID
  2.  
Wow ! it work !, thanks a millions, btw I really don;t understand the single quote and the double quote combination, would you mind explain a little or any website that I could understand a bit more on the single and double quote combination.
Dec 18 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
RunSQL requires a string. To delimit something as a string, you surround it with either double quotes or single quotes.

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1"
  2. 'SELECT * FROM Table1'
However, what the SQL processor sees it without the quotes.
But, when the SQL engine is processing the statement, if it's expecting a string, then you have to tell it that it is a string by surrounding it with either single quotes or double quotes.

However, if you are already delineating a string using one of the quotes, then if you need to delineate another string, you need to use the other quote or double up on the quote.

All of the below are syntactically correct.
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1 WHERE Field1 = 'x'"
  2. 'SELECT * FROM Table1 WHERE Field1 = "x"'
  3. "SELECT * FROM Table1 WHERE Field1 = ""x"""
  4. 'SELECT * FROM Table1 WHERE Field1 = ''x'''
  5.  
RunSQL gets:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1 WHERE Field1 = 'x'"
But RunSQL uses the SQL engine. The SQL engine processes:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Table1 WHERE Field1 = 'x'
  2.  
So without the quotes to tell the engine that 'x' is actually a string and not a variable, then it will try to find that variable named x.
Dec 18 '07 #4

100+
P: 134
RunSQL requires a string. To delimit something as a string, you surround it with either double quotes or single quotes.

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1"
  2. 'SELECT * FROM Table1'
However, what the SQL processor sees it without the quotes.
But, when the SQL engine is processing the statement, if it's expecting a string, then you have to tell it that it is a string by surrounding it with either single quotes or double quotes.

However, if you are already delineating a string using one of the quotes, then if you need to delineate another string, you need to use the other quote or double up on the quote.

All of the below are syntactically correct.
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1 WHERE Field1 = 'x'"
  2. 'SELECT * FROM Table1 WHERE Field1 = "x"'
  3. "SELECT * FROM Table1 WHERE Field1 = ""x"""
  4. 'SELECT * FROM Table1 WHERE Field1 = ''x'''
  5.  
RunSQL gets:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM Table1 WHERE Field1 = 'x'"
But RunSQL uses the SQL engine. The SQL engine processes:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Table1 WHERE Field1 = 'x'
  2.  
So without the quotes to tell the engine that 'x' is actually a string and not a variable, then it will try to find that variable named x.

Thank you so much for writing a wonderful and a careful though explanation, I will try to figure out
Dec 18 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.