SQL Statement with WHERE clause not working in VBA code

[DMUM via AccessMonster.com]

Hi, I am using a SQL statement as the record source for a list box on my form.


This is my query
strSQl = "SELECT tblTemp_NewDefs.Defnum, tblTemp_NewDefs.PRSA_Definition,
tblTemp_NewDefs.PRSA_Type " & _
"FROM tblTemp_NewDefs " & _
"WHERE tblTemp_NewDefs.PRSA_Type=Risk);"

Originally when I ran this, I kept getting the pop up message to provide a
value for Risk. Now it just runs through but provides no data. When I use
the " "'s around Risk as in
"WHERE tblTemp_NewDefs.PRSA_Type="Risk");"

the code is not accepted by the VBA code window (it turns red in my case)

I originally had the SQL string using a variable for "Risk" instead of the
word since I have to run the same query 4 times but with different criteria
for the PRSA_Type. I used:

"WHERE tblTemp_NewDefs.PRSA_Type= " & procName & ");" where procName =
"Risk"

Here again I get the pop up window requesting a value for Risk

This is quite frustrating because I don't have a clue as to why it isn't
accepting the value

 

[DMUM via AccessMonster.com]

Never Mind - ugh!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I found my error!

 

[fredg]

Hi, I am using a SQL statement as the record source for a list box on my form.

This is my query
strSQl = "SELECT tblTemp_NewDefs.Defnum, tblTemp_NewDefs.PRSA_Definition,
tblTemp_NewDefs.PRSA_Type " & _
"FROM tblTemp_NewDefs " & _
"WHERE tblTemp_NewDefs.PRSA_Type=Risk);"

Originally when I ran this, I kept getting the pop up message to provide a
value for Risk. Now it just runs through but provides no data. When I use
the " "'s around Risk as in
"WHERE tblTemp_NewDefs.PRSA_Type="Risk");"

the code is not accepted by the VBA code window (it turns red in my case)

I originally had the SQL string using a variable for "Risk" instead of the
word since I have to run the same query 4 times but with different criteria
for the PRSA_Type. I used:

"WHERE tblTemp_NewDefs.PRSA_Type= " & procName & ");" where procName =
"Risk"

Here again I get the pop up window requesting a value for Risk

This is quite frustrating because I don't have a clue as to why it isn't
accepting the value

PRSA_Type is a Text datatype so the value must be placed within
quotes.
However, the entire SQL itself is within quotes, so Access thinks the
end of the string is Type=" and everything after that is in error.
Use single quotes instead:

"WHERE tblTemp_NewDefs.PRSA_Type= 'Risk');"

 

[Ricky Hicks [MVP]]

Try ....

WHERE tblTemp_NewDefs.PRSA_Type='Risk';"

If you want to referenece the variable try ...

"WHERE tblTemp_NewDefs.PRSA_Type= '" & procName & "';"

 

[tina]

first of all, you need to get rid of the closing parenthesis, because
there's no opening parenthesis to match it. next, whether a text value is
hard-coded, or concatenated into the SQL string using a control reference or
a variable, the text value needs to be enclosed in single quotes *inside*
the double-quoted SQL string, as

strSQl = "SELECT tblTemp_NewDefs.Defnum, tblTemp_NewDefs.PRSA_Definition,
tblTemp_NewDefs.PRSA_Type " & _
"FROM tblTemp_NewDefs " & _
"WHERE tblTemp_NewDefs.PRSA_Type='Risk';"

or, when a variable is used, as

"WHERE tblTemp_NewDefs.PRSA_Type= '" & procName & "';"

hth

 

[DMUM via AccessMonster.com]

Yes the closing parenthesis was causing my problem here. I guess I kept
playing around with it so much, I didn't notice when I left it there without
an opening one.

Thanks to everyone that responded.

first of all, you need to get rid of the closing parenthesis, because
there's no opening parenthesis to match it. next, whether a text value is
hard-coded, or concatenated into the SQL string using a control reference or
a variable, the text value needs to be enclosed in single quotes *inside*
the double-quoted SQL string, as

strSQl = "SELECT tblTemp_NewDefs.Defnum, tblTemp_NewDefs.PRSA_Definition,
tblTemp_NewDefs.PRSA_Type " & _
"FROM tblTemp_NewDefs " & _
"WHERE tblTemp_NewDefs.PRSA_Type='Risk';"

or, when a variable is used, as

"WHERE tblTemp_NewDefs.PRSA_Type= '" & procName & "';"

hth

Hi, I am using a SQL statement as the record source for a list box on my form.

[quoted text clipped - 22 lines]

This is quite frustrating because I don't have a clue as to why it isn't
accepting the value

 

[DMUM via AccessMonster.com]

Ahhhh!. Kept using ""RISK"" then """RISK""" don't think I tried the single
quotes.

Thank you

Hi, I am using a SQL statement as the record source for a list box on my form.

[quoted text clipped - 22 lines]

This is quite frustrating because I don't have a clue as to why it isn't
accepting the value

PRSA_Type is a Text datatype so the value must be placed within
quotes.
However, the entire SQL itself is within quotes, so Access thinks the
end of the string is Type=" and everything after that is in error.
Use single quotes instead:

"WHERE tblTemp_NewDefs.PRSA_Type= 'Risk');"