RunSQL help needed

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

Guest

I have the following code which when run prompts to the value of
'strLocation'. Can someone tell me what I am missing. Thank you in
advance.....

-------------------
Dim strLocation As Integer

strLocation = Left(Me!AssignedTo, 3)

DoCmd.RunSQL "INSERT INTO tblVehicles(VehicleStatus, VehicleNumber,
Location, Disposition) VALUES('In Service', [VehicleNumber], strLocation,
[AssignedTo])"

---------------------

The value of strLocation is correctly assigned, but I am still prompted to
enter it.

Cheers
 
Okay, but when I put in a break, it shows the value of strLocation as the
first three digits of AssignedTo.

Either way, I am still prompted with a popup to enter the value of
strLocation. Do I have the syntax correct for inserting a variable?

Cheers

PC Datasheet said:
The Left function returns a string so you need to Dim strLocation as String.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Paul B. said:
I have the following code which when run prompts to the value of
'strLocation'. Can someone tell me what I am missing. Thank you in
advance.....

-------------------
Dim strLocation As Integer

strLocation = Left(Me!AssignedTo, 3)

DoCmd.RunSQL "INSERT INTO tblVehicles(VehicleStatus, VehicleNumber,
Location, Disposition) VALUES('In Service', [VehicleNumber], strLocation,
[AssignedTo])"

---------------------

The value of strLocation is correctly assigned, but I am still prompted to
enter it.

Cheers
Click to expand...
Click to expand...
 
In addition to the point that 'PC Datasheet' makes elsewhere in this thread
about the data type of the 'strLocation' variable, you have the name of the
variable inside the quotes delimiting the SQL string. That will result in
adding the *name* of the variable, the literal text 'strLocation' into the
string instead of the *value* stored in the variable. You need something
like ...

" ... [VehicleNumber], " & strLocation & ", ... "

.... if the value is a number or ...

"...[VehicleNumber], '" & strLocation & "', ..."

.... if the value is a string.

In the second example, that's a single quote followed by a double quote
before the first ampersand, and a double qote followed by a single quote
after the second ampersand.

A useful technique for debugging this type of problem is to assign the SQL
statement to a string and Debug.Print the result the Immediate window, so
you can see what the finished string looks like, e.g. ...

strSQL = <build your SQL statement here>
Debug.Print strSQL

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan Reynolds said:
In addition to the point that 'PC Datasheet' makes elsewhere in this
thread about the data type of the 'strLocation' variable, you have the
name of the variable inside the quotes delimiting the SQL string. That
will result in adding the *name* of the variable, the literal text
'strLocation' into the string instead of the *value* stored in the
variable. You need something like ...

" ... [VehicleNumber], " & strLocation & ", ... "

... if the value is a number or ...

"...[VehicleNumber], '" & strLocation & "', ..."

... if the value is a string.

In the second example, that's a single quote followed by a double quote
before the first ampersand, and a double qote followed by a single quote
after the second ampersand.

A useful technique for debugging this type of problem is to assign the SQL
statement to a string and Debug.Print the result the Immediate window, so
you can see what the finished string looks like, e.g. ...

strSQL = <build your SQL statement here>
Debug.Print strSQL
Click to expand...



In addition to these points, you may have to take into account the
possibility of a double quote appearing in strLocation before you even start
to build the SQL string - if this happens the statement will fail. Perhaps
this is unlikely (impossible you say) but there could be a number of things
that could cause the query to fail so in general, you should include error
handling. Perhaps you could write a function AddVehicle() which returns
true or false to indicate whether the record really got added or not. Post
if you need details of this.
 
Thanks Brendan, that did it.

Cheers


Brendan Reynolds said:
In addition to the point that 'PC Datasheet' makes elsewhere in this thread
about the data type of the 'strLocation' variable, you have the name of the
variable inside the quotes delimiting the SQL string. That will result in
adding the *name* of the variable, the literal text 'strLocation' into the
string instead of the *value* stored in the variable. You need something
like ...

" ... [VehicleNumber], " & strLocation & ", ... "

.... if the value is a number or ...

"...[VehicleNumber], '" & strLocation & "', ..."

.... if the value is a string.

In the second example, that's a single quote followed by a double quote
before the first ampersand, and a double qote followed by a single quote
after the second ampersand.

A useful technique for debugging this type of problem is to assign the SQL
statement to a string and Debug.Print the result the Immediate window, so
you can see what the finished string looks like, e.g. ...

strSQL = <build your SQL statement here>
Debug.Print strSQL

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Paul B. said:
I have the following code which when run prompts to the value of
'strLocation'. Can someone tell me what I am missing. Thank you in
advance.....

-------------------
Dim strLocation As Integer

strLocation = Left(Me!AssignedTo, 3)

DoCmd.RunSQL "INSERT INTO tblVehicles(VehicleStatus, VehicleNumber,
Location, Disposition) VALUES('In Service', [VehicleNumber], strLocation,
[AssignedTo])"

---------------------

The value of strLocation is correctly assigned, but I am still prompted to
enter it.

Cheers
Click to expand...
Click to expand...
 
Eric Schittlipz said:
In addition to these points, you may have to take into account the
possibility of a double quote appearing in strLocation before you even start
to build the SQL string - if this happens the statement will fail. Perhaps
this is unlikely (impossible you say) but there could be a number of things
that could cause the query to fail so in general, you should include error
handling. Perhaps you could write a function AddVehicle() which returns
true or false to indicate whether the record really got added or not. Post
if you need details of this.
Click to expand...


Yes please and Thanks! I haven't got around to understanding error handling
yet.

Cheers
 
Eric Schittlipz said:
In addition to these points, you may have to take into account the
possibility of a double quote appearing in strLocation before you even start
to build the SQL string - if this happens the statement will fail. Perhaps
this is unlikely (impossible you say) but there could be a number of things
that could cause the query to fail so in general, you should include error
handling. Perhaps you could write a function AddVehicle() which returns
true or false to indicate whether the record really got added or not. Post
if you need details of this.
Click to expand...

I'm having a similar problem. Working in Access 2003 on a legacy system in
which some critical fields may contain single or double quotes (abbreviating
feet or inches), I am trying to programmatically compare newly-imported data
with existing tables. If a match is found, I update the existing record; if
a match is not found, I append the new record.

It all works, EXCEPT for fields containing double quotes.

Can you suggest a way for me to handle these troublesome fields? I've been
kicking around some ideas using InStr or Replace, but I haven't made much
progress.

Thanks in advance!

-mary
 
Back
Top