Sql punctuation

[Accelerations]

I have this statement strSQL = "SELECT * FROM AllResults where level =
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Thank-you.

 

[Marshall Barton]

I have this statement strSQL = "SELECT * FROM AllResults where level =
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Depends on what you are doing with the strSQL variable. If
you are stuffing it into an Access object's ...Source
property, it should work. If you are using it in a VBA
procedure to open a recordset, you need to resolve the
parameters before opening the recordset.

Alternatively, as long as level list and group list are not
null, you could use:

strSQL = "SELECT * FROM AllResults " & _
"WHERE level = " & Me.levellist & _
" AND agegroup = """ & Me.agegroupList & """"

 

[Accelerations]

Thanks Marshall that worked. I'm using two list boxes on a different form to
run the sql. I thought you used single quotes around text and double quotes
around numbers? I'd like to learn more about using variables in sql. Do you
have any suggestions for books or web sites? Thanks again.

I have this statement strSQL = "SELECT * FROM AllResults where level
=
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but
said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Depends on what you are doing with the strSQL variable. If
you are stuffing it into an Access object's ...Source
property, it should work. If you are using it in a VBA
procedure to open a recordset, you need to resolve the
parameters before opening the recordset.

Alternatively, as long as level list and group list are not
null, you could use:

strSQL = "SELECT * FROM AllResults " & _
"WHERE level = " & Me.levellist & _
" AND agegroup = """ & Me.agegroupList & """"

 

[John Vinson]

I have this statement strSQL = "SELECT * FROM AllResults where level =
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Thank-you.

Try putting in explicit singlequote delimiters:

strSQL = "SELECT * FROM AllResults where [level] ='" &
[Forms]![getresults]![levellist] & "' and agegroup =" &
[Forms]![getresults]![agegroupList] & ";"


John W. Vinson[MVP]

 

[Accelerations]

John I ran your response and I got syntax error missing operator. The
message box has four single quotes in it. If it makes a difference I'm using
Office 2003 ps with XP Pro sp2.

I have this statement strSQL = "SELECT * FROM AllResults where level
=
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but
said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Thank-you.

Try putting in explicit singlequote delimiters:

strSQL = "SELECT * FROM AllResults where [level] ='" &
[Forms]![getresults]![levellist] & "' and agegroup =" &
[Forms]![getresults]![agegroupList] & ";"


John W. Vinson[MVP]

 

[John Vinson]

John I ran your response and I got syntax error missing operator. The
message box has four single quotes in it. If it makes a difference I'm using
Office 2003 ps with XP Pro sp2.

Sounds like the form isn't open, or the controls you're referencing
are empty.

Crossthreaded here: Text fields must be delimited by either ' or ",
your choice (though either " or some more complex trickery is needed
if your criterion might contain an apostrophe); Number fields do not
use any delimiter.

John W. Vinson[MVP]

 

[Accelerations]

It's working fine now. Thanks for you help.

Sounds like the form isn't open, or the controls you're referencing
are empty.

Crossthreaded here: Text fields must be delimited by either ' or ",
your choice (though either " or some more complex trickery is needed
if your criterion might contain an apostrophe); Number fields do not
use any delimiter.

John W. Vinson[MVP]

 

[Marshall Barton]

You never use quotes around a number and always use quotes
around text. In SQL, whether you use single or double
quotes is your call. Just remember that if the text
contains the quote you are using around the text, you have
to double up the one(s) inside the text. I use double
quotes around text because it's more likely that some text
will contain a single quote (used as an apostrophe, e.g.
O'Brian).
--
Marsh
MVP [MS Access]

Thanks Marshall that worked. I'm using two list boxes on a different form to
run the sql. I thought you used single quotes around text and double quotes
around numbers? I'd like to learn more about using variables in sql. Do you
have any suggestions for books or web sites? Thanks again.


"Marshall Barton" wrote

I have this statement strSQL = "SELECT * FROM AllResults where level
=
(" & [Forms]![getresults]![levellist] & ") and agegroup =
[Forms]![getresults]![agegroupList]". The levellist is a number and that
works. The agegrouplist is text and I can't get it right. I know it's the
placement of the single quote. On a couple of tries it ran the code but
said
no records were found. Most of the time I get a syntax error. I know it is
simple but any help is appreciated.

Depends on what you are doing with the strSQL variable. If
you are stuffing it into an Access object's ...Source
property, it should work. If you are using it in a VBA
procedure to open a recordset, you need to resolve the
parameters before opening the recordset.

Alternatively, as long as level list and group list are not
null, you could use:

strSQL = "SELECT * FROM AllResults " & _
"WHERE level = " & Me.levellist & _
" AND agegroup = """ & Me.agegroupList & """"