Execute SQL SELECT statement with VBA code

[Sid]

One more....
What might be wrong with my syntax, as I try to use the Find "*" at the end
of a line like this:
strSQL = strSQL & " AND Field1 = " & " LIKE "*"" ?

This line would follow a line that works perfectly alone like:
strSQL = strSQL & " WHERE Field2 = " & """" & Me!Select & """"
Thanks

I'm trying to string several strSQLs together for a custom report query.

Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid

The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the word,
not using your field name. Surround Group with [ ] in the SQL statement and
it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named it
Groups & it works!
Thanks
Michael
Would that have been the reference: Microsoft ADO Ext. 2.1 for DDL and
Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in response to
selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the Me!SelectGrp has
never
worked. This is awful, there must be a non-experimental syntax I can
obtain
for SQL.
Thanks

Hi Sid,

I'm not quite sure, but you can try this:

strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
strSQL = strSQL & " AND Group = " & """" & Me!SelectGrp & """"

In the WHERE statement, you can have a combination of different
conditions,
all linked by eithere AND or OR. I put here AND for an example,

but
you

can
also have a OR in place, depending of how you want to select the data.
Use
AND when you want both conditions to be true at the same time, OR for
either
one of them to be true.

For the "Dim rst As ADODB.Recordset" to work, you have to

reference
ADO

in
you vba module.

Hope this might help

Ken
I have learned a bit since my posting yesterday. I was trying to
display
a
string variable (in a form text box for a test)with a string of SQL
characters, couldn't. But now I know the SQL syntax is super
critical.
I
managed to make the following work:
-----
Private Sub Command37_Click() ' SQL Creator Test
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
'DoCmd.DeleteObject acQuery, "qryTempRptQry"
strSQL = "SELECT * FROM qryReportLog1"
'Works by itself
'strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
'Works by itself
'strSQL = strSQL & " WHERE Group = " & """" & Me!SelectGrp & """"
'WON'T
work
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly
Set qdf = Nothing
Set dbs = Nothing

lost

as

trying

to input
at: double
up of
the My
goal Glad
you but
keep run
it

 

[Ken Snell]

You need to double up the " marks when you embed them in a string (or use '
mark instead):

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"" "

or

strSQL = strSQL & " AND Field1 = " & " LIKE '*'"


--
Ken Snell
<MS ACCESS MVP>

One more....
What might be wrong with my syntax, as I try to use the Find "*" at the end
of a line like this:
strSQL = strSQL & " AND Field1 = " & " LIKE "*"" ?

This line would follow a line that works perfectly alone like:
strSQL = strSQL & " WHERE Field2 = " & """" & Me!Select & """"
Thanks

I'm trying to string several strSQLs together for a custom report query.

Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid

The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the word,
not using your field name. Surround Group with [ ] in the SQL

statement
and

it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I

named
it

Groups & it works!
Thanks
Michael
Would that have been the reference: Microsoft ADO Ext. 2.1 for

DDL
and

Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in response to
selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the Me!SelectGrp has
never
worked. This is awful, there must be a non-experimental syntax I can
obtain
for SQL.
Thanks

Hi Sid,

I'm not quite sure, but you can try this:

strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
strSQL = strSQL & " AND Group = " & """" & Me!SelectGrp & """"

In the WHERE statement, you can have a combination of different
conditions,
all linked by eithere AND or OR. I put here AND for an example, but
you
can
also have a OR in place, depending of how you want to select the data.
Use
AND when you want both conditions to be true at the same time,

OR
for

either
one of them to be true.

For the "Dim rst As ADODB.Recordset" to work, you have to reference
ADO
in
you vba module.

Hope this might help

Ken
I have learned a bit since my posting yesterday. I was trying to
display
a
string variable (in a form text box for a test)with a string

of
SQL

characters, couldn't. But now I know the SQL syntax is super
critical.
I
managed to make the following work:
-----
Private Sub Command37_Click() ' SQL Creator Test
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
'DoCmd.DeleteObject acQuery, "qryTempRptQry"
strSQL = "SELECT * FROM qryReportLog1"
'Works by itself
'strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
'Works by itself
'strSQL = strSQL & " WHERE Group = " & """" & Me!SelectGrp & """"
'WON'T
work
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly
Set qdf = Nothing
Set dbs = Nothing

lost

as
to
why
the SelectPnt one works and the second one won't. Now I'm

trying

to
get
some code working that gives the prefix & suffix for a given input
at:
http://www.mvps.org/access/forms/frm0001.htm The "Dim rst As
ADODB.Recordset" gives a 'User defined type, not defined'

error
when

compiled. The tangents never end... Thanks.

Sid

Post example of what you're trying to do. It's definitely possible
to
do
what you want to do, but you may be running into problem of
needing
to
embed
" characters inside the string? If that is it, be sure to double
up
the
"
characters ("") in order to have a single " character be

part

of the
new wrote
in

 

[Ken Snell]

Note that there shouldn't be a space in front of the last " in my example:

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"""

--
Ken Snell
<MS ACCESS MVP>

You need to double up the " marks when you embed them in a string (or use '
mark instead):

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"" "

or

strSQL = strSQL & " AND Field1 = " & " LIKE '*'"


--
Ken Snell
<MS ACCESS MVP>

One more....
What might be wrong with my syntax, as I try to use the Find "*" at the end
of a line like this:
strSQL = strSQL & " AND Field1 = " & " LIKE "*"" ?

This line would follow a line that works perfectly alone like:
strSQL = strSQL & " WHERE Field2 = " & """" & Me!Select & """"
Thanks

I'm trying to string several strSQLs together for a custom report query.

Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid
The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the
word,
not using your field name. Surround Group with [ ] in the SQL statement
and
it should work ok...better thing to do though is to not use it as a field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named
it
Groups & it works!
Thanks
Michael
Would that have been the reference: Microsoft ADO Ext. 2.1 for DDL
and
Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in response to
selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the

Me!SelectGrp
has

never
worked. This is awful, there must be a non-experimental syntax

I
can

obtain
for SQL.
Thanks

Hi Sid,

I'm not quite sure, but you can try this:

strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
strSQL = strSQL & " AND Group = " & """" & Me!SelectGrp & """"

In the WHERE statement, you can have a combination of different
conditions,
all linked by eithere AND or OR. I put here AND for an

example,
but

you
can
also have a OR in place, depending of how you want to select the
data.
Use
AND when you want both conditions to be true at the same time, OR
for
either
one of them to be true.

For the "Dim rst As ADODB.Recordset" to work, you have to reference
ADO
in
you vba module.

Hope this might help

Ken
I have learned a bit since my posting yesterday. I was

trying
to

display
a
string variable (in a form text box for a test)with a string of
SQL
characters, couldn't. But now I know the SQL syntax is super
critical.
I
managed to make the following work:
-----
Private Sub Command37_Click() ' SQL Creator Test
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
'DoCmd.DeleteObject acQuery, "qryTempRptQry"
strSQL = "SELECT * FROM qryReportLog1"
'Works by itself
'strSQL = strSQL & " WHERE Label = " & """" & Me!SelectPnt & """"
'Works by itself
'strSQL = strSQL & " WHERE Group = " & """" & Me!SelectGrp & """"
'WON'T
work
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("qryTempRptQry", strSQL)
DoCmd.OpenQuery "qryTempRptQry", , acReadOnly
Set qdf = Nothing
Set dbs = Nothing
------
The Me!SelectPnt & Me!SelectGrp are combo boxes that select
choices
like
"Room 103", and "Physical Plant" respectively -- I'm totally lost
as
to
why
the SelectPnt one works and the second one won't. Now I'm trying
to
get
some code working that gives the prefix & suffix for a given input
at:
http://www.mvps.org/access/forms/frm0001.htm The "Dim rst As
ADODB.Recordset" gives a 'User defined type, not defined' error
when
compiled. The tangents never end... Thanks.

Sid

Post example of what you're trying to do. It's definitely
possible
to
do
what you want to do, but you may be running into problem of
needing
to
embed
" characters inside the string? If that is it, be sure to double
up
the
"
characters ("") in order to have a single " character be

part

of
the
text
string.

--
Ken Snell
<MS ACCESS MVP>

Thanks Ken
But one more question for the Guru.
I'm trying to set a string of text(for an SQL) equal to a
variable,
to
ultimately compose the SQL for my QueryDef. BUT, a string
variable
will
not
accept a data in even a simple SQL line, including quotes,
parentheses,
asterisks, single quotes, and all that you find in a

SQL.
My

goal
is
to
create 4 different variables based on selections for

from

a query,
but wrote
in then
run

 

[Douglas J. Steele]

Actually, Ken, the space doesn't really matter. It'll just add white space
in a "safe position" (i.e.: between predicates in the SQL statement).

However, the equal signs matter. You can't use = and Like together.

strSQL = strSQL & " AND Field1 LIKE ""*"""

or

strSQL = strSQL & " AND Field1 LIKE '*'"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Note that there shouldn't be a space in front of the last " in my example:

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"""

--
Ken Snell
<MS ACCESS MVP>

You need to double up the " marks when you embed them in a string (or

use

'
mark instead):

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"" "

or

strSQL = strSQL & " AND Field1 = " & " LIKE '*'"


--
Ken Snell
<MS ACCESS MVP>

One more....
What might be wrong with my syntax, as I try to use the Find "*" at

the
end

of a line like this:
strSQL = strSQL & " AND Field1 = " & " LIKE "*"" ?

This line would follow a line that works perfectly alone like:
strSQL = strSQL & " WHERE Field2 = " & """" & Me!Select & """"
Thanks

I'm trying to string several strSQLs together for a custom report query.
Thanks Ken
I just changed the field name in the data table to Groups.
Everything is working amazingly well now.
Thanks for your time, that was a weird one. I need to learn all SQL &
other reserved words to avoid.
Sid
The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use of the
word,
not using your field name. Surround Group with [ ] in the SQL statement
and
it should work ok...better thing to do though is to not use it as a
field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the problem. I named
it
Groups & it works!
Thanks
Michael
Would that have been the reference: Microsoft ADO Ext. 2.1

for
DDL

and
Security ?
I selected it, but I still get the compile error.

My SQL problem is running those strings separately, (in

response
to

selections on my project form), but my test was just trying each
individually. The Me!SelectPnt one worked, while the Me!SelectGrp
has
never
worked. This is awful, there must be a non-experimental

syntax

I time,
OR string
of

to

a from string
to add
the

query)

is

 

[Ken Snell]

Thanks for the eagle eyes -- copying and pasting strikes again!

--
Ken Snell
<MS ACCESS MVP>

Actually, Ken, the space doesn't really matter. It'll just add white space
in a "safe position" (i.e.: between predicates in the SQL statement).

However, the equal signs matter. You can't use = and Like together.

strSQL = strSQL & " AND Field1 LIKE ""*"""

or

strSQL = strSQL & " AND Field1 LIKE '*'"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Note that there shouldn't be a space in front of the last " in my example:

strSQL = strSQL & " AND Field1 = " & " LIKE ""*"""

--
Ken Snell
<MS ACCESS MVP>

use

SQL

&

other reserved words to avoid.
Sid
The reason Group doesn't work is because it's a "reserved word" in
ACCESS....so ACCESS thinks that you're calling that special use

of
the

word,
not using your field name. Surround Group with [ ] in the SQL
statement
and
it should work ok...better thing to do though is to not use it

as

a syntax &
"""" select
the

Me!SelectPnt

&

Me!SelectGrp

& rst
As problem
of to grin!
. behind
the

line

I and
then it
when query)

 

[Sid]

Thanks Ken & Doug.
I have mastered it. I now have a report select form with four option
group/selections, all working as dreamt.
Thanks for your time -- I'm sleeping much better now.
Sid

Thanks for the eagle eyes -- copying and pasting strikes again!

--
Ken Snell
<MS ACCESS MVP>

Actually, Ken, the space doesn't really matter. It'll just add white space
in a "safe position" (i.e.: between predicates in the SQL statement).

However, the equal signs matter. You can't use = and Like together.

strSQL = strSQL & " AND Field1 LIKE ""*"""

or

strSQL = strSQL & " AND Field1 LIKE '*'"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



(or
use at
the SQL

word"

in

use

of

the
word,
not using your field name. Surround Group with [ ] in the SQL
statement
and
it should work ok...better thing to do though is to not use it

as

a
field
name.

See this MS KB article for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;EN-US;286335

--
Ken Snell
<MS ACCESS MVP>

Amazing!
I found my problem Ken &/or Michael.
The word "Group"(my field name) in my SQL caused the

problem.

I 2.1
for trying
each

Me!SelectPnt

& &
"""" Me!SelectPnt Me!SelectGrp sure
to

character

be equal
to wrote
in line will
add wrote
in

 

[Ken Snell]

Congratulations!