SQL Insert Values - quote problem

  • Thread starter Thread starter Penstar
  • Start date Start date
P

Penstar

I have the following SQL to insert into a table:

strSQL = "INSERT INTO log " & "(origin, to, mobile, result) " & "VALUES(" &
"Mobile List" & ", " & firstname & " " & lastname & ", " & Mobile & ", " &
getresponse & ")"
CurrentDb.Execute strSQL, dbFailOnError

In the immediate window strSQL shows the following result
strSQL=INSERT INTO log (origin, to, mobile, result) VALUES(Mobile List, John
Smith, 0420000000, +OK)

I am thinking it is not working because the values don't have quotation
marks around them. I cannot figure out how to put these in in my SQL string.
(All values are text)

Any help would be much appreciated.
Thanks
Penny
 
I have a function I use that helps with building SQL strings that contain
text values. The problem is that you have to wrap text type values in quotes
or single quotes in your SQL string. This can be very difficult to read, so
I created a function that will return a string, wrapped in quotes.

Public Function Quotes(TextToWrap As Variant, _
Optional WrapWith As Variant = Null) As
String

'Returns an empty string if a NULL value was passed as the TextToWrap
value
If IsNull(WrapWith) Then WrapWith = Chr$(34)

Quotes = WrapWith _
& Replace(Nz(TextToWrap, ""), WrapWith, WrapWith & WrapWith) _
& WrapWith

End Function

The way you would use this function is:

strSQL = "INSERT INTO log (origin, to, mobile, result) " _
& "VALUES(" & Quotes("Mobile List") & ", " _
& Quotes([Firstname]) & ", " _
& Quotes([Lastname]) & ", " _
& Quotes([Mobile]) & ", "
& Quotes([GetResponse]) & ")"

I'm not entirely sure about the "GetResponse" value. If it is a text value
then this should work.

Another advantage with this is that you can change the WrapWith parameter to
# if you want to insert a date value.

HTH
Dale
"Mobile List" & ", " & firstname & " " & lastname & ", " & Mobile & ", " &
getresponse & ")"
CurrentDb.Execute strSQL, dbFailOnError

In the immediate window strSQL shows the following result
strSQL=INSERT INTO log (origin, to, mobile, result) VALUES(Mobile List, John
Smith, 0420000000, +OK)
Click to expand...


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Thank you Daniel. The allenbrowne code helped get me 90% of the way there (a
very handy tool)

Access Vandal: "Mobile List" is just text to be added to the table. Once I
got the quotes right the + didn't give me any problems, but the
Right(getresponse, 2) does appear the correct way to go. Thanks

Dale. Thanks for your function. I will try it next time I have problems
(and believe me there will be a next time!)
 
Back
Top