Why the extra "

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

This code works.

Private Sub AllIssuesRole_Click()
If Me!AllIssuesRole = True Then
DoCmd.RunSQL "INSERT INTO dbo_UsersRoles (Role,UserName) _
VALUES (" & Me!AllIssues & ",""" & Me!UserName & """);"
End If
End Sub

My question is this. Me!AllIssues and Me!UserName are both varchar(50), so
why do I have to put the 2 extra " at the beginning and end of """ &
Me!UserName & """ to prevent the "Enter perameter value" promt for UserName?
 
Longer answer is because you are using two different
languages (VBA and SQL) that are executed independently. To
get VBA to include a quote within quotes, you need two
quotes, which will result in one quote.

First, your copy/paste and putting the code on two lines
made it syntactically invalid. To do that on two lines, it
should be:

.... "INSERT INTO dbo_UsersRoles (Role,UserName) " _
& "VALUES (" & Me!AllIssues & ",""" & Me!UserName & """);"

The result of the VBA concatenations would be something
like:

INSERT INTO dbo_UsersRoles (Role,UserName)
VALUES (1234,"Ryan");"

which is a valid SQL statement, but only if Role is either a
numeric type field or SQL Server automatically converts 1234
to a string (Jet will not do that in this case).

If Me!AllIssues contained alphabetic characters (e.g.
Sales), then the resulting SQL statement would be:

INSERT INTO dbo_UsersRoles (Role,UserName)
VALUES (Sales,"Ryan");"

which would probably give you an unknown field name error.

If Role really is a text type field, then, even if its value
are all digits, you should also use the quotes around
Me!AllIssues:

.... "INSERT INTO dbo_UsersRoles (Role,UserName) " _
& "VALUES (""" & Me!AllIssues & """,""" & Me!UserName _
& """);"
 
Thank you very much for the answer Marshall. Do you know of any good
resources to learn concantination syntax? I do a good job repeating what I
find on the forums, but I really dont know WHY it works, I just know how to
repeate what I have seen. Like I said, the line of code I was using was
working, but so did this one that you posted. DoCmd.RunSQL "INSERT INTO
dbo_UsersRoles (Role,UserName) " _
& "VALUES (""" & Me!AllIssues & """,""" & Me!UserName _
& """);"
I will use it since you know what your doing haha.
 
Access and Jet are very picky about delimiting values in SQL statments and in
domain aggragate functions. The delimiters required are based on the data
type of the field in the table.
Boolean (Yes/No) and all numeric data types require no delimiters.
Date field require an pound sign.
Text fields require single or double quotes

With numeric fields it is easy:

strSQL = "SELECT * FROM AnyTable WHERE TransAmt > " & Me.txtTransAmt & ";"

When you want to use line continuation to make the code more readable and
you have to break what would be one string into parts, you concatenate
multiple strings into one. Be sure to include spaces where necessary:

strSQL = "SELECT * FROM AnyTable " & _
"WHERE TransAmt > " & Me.txtTransAmt & ";"

Would result in:
"SELECT * FROM AnyTable WHERE TransAmt > 335.67;"

Dates are pretty straight forward as well

strSQL = "SELECT * FROM AnyTable WHERE TransDate = #" & _
Me.txtTransAmt & "#;"

Would result in:

"SELECT * FROM AnyTable WHERE TransDate = #5/25/2008#;"

Now, strings are the one that throws everyone at first.

strSQL = "SELECT * FROM AnyTable WHERE TransCategory = '" & _
Me.txtTransCategory & "';"

Note, it uses single qoutes, which is easier because they do not delimit a
string in VBA and the sytanx is easily understood. It would result in:

"SELECT * FROM AnyTable WHERE TransCategory = 'ABC';"

Now, in most cases that will work just fine---Except where you might find a
' in the data. For example in name fields, the first time it tries to match
on O' Tool or O' Reilly, it is going to choke. So it is a better practice to
always enclose strings in double qoutes.

strSQL = "SELECT * FROM AnyTable WHERE TransCategory = """ & _
Me.txtTransCategory & """;"

AAARRRRGGGGHHHH!!!! How do I know where to put the quotes, I keep getting
syntax errors. This used to drive me crazy. I saw and read a lot of tricks
to do it like using Chr(34) which is a quote mark or constansts the evaluate
to a quote mark, but they seemed to med to make no more sense than the
original problem. But then, in one of those rare flashes on inspiration, I
fell on a way that worked for me until I finally got used to it and seldom
have to fall back on it any more.
First, code the string using the single quotes. No Problem.
Now, go back and edit the string, replacing each single qoute with two
double qoutes. So: "'" becomes """"

At least it works for me.
(But if they would fire that O' Brien guy I wouldn't have to worry about it
any more<g>)

One final note that may or may not be obvious:
The reason to break up the string in the first place is to get the value of
the object in the string rather than the name of the object. For example:

strSQL = "SELECT * FROM AnyTable WHERE TransAmt > Me.txtTransAmt;"

would result in:
"SELECT * FROM AnyTable WHERE TransAmt > Me.txtTransAmt;"

which would be meaningless to Jet. In the previous example, you saw where
it replace the reference to the control with the value of the control.
 
Back
Top