Still having probs with SQL and Variables...

[Guest]

Sorry to keep bothering you guys but I really hope you can help me out yet
again before my hair has completely fallen out.
I've got over 20 queries that I want vb code to 'build' via SQL. The
queries are very similar in that they are counting the number of records
within a particular field. I'm trying to get the code to loop through each
query and to refer to each (different) table field name by storing it in a
variable.
Here's the code:

st1 = "name of table field 1"
st2 = "name of table field 2"
st3 = "name of table field 3"
st4 = "name of table field 4" ...and so on up to st22

intQueryRef = 1
intStrRef = 1

Do Until intQueryRef = 23
stDocName = "qry_Stats" & intQueryRef
Set loqd = CurrentDb.QueryDefs(stDocName)
stSQL = "SELECT Count(tbl_Reports.Number) AS CountOfNumber "
stSQL = stSQL & "FROM tbl_Reports LEFT JOIN qry_Entries ON
tbl_Reports.Number = qry_Entries.Number "
stSQL = stSQL & "WHERE tbl_Reports." & " & st" & intStrRef & " = True;"
loqd.SQL = stSQL
intQueryRef = intQueryRef + 1
intStrRef = intStrRef + 1
Loop

However, I can't seem to get the WHERE clause to refer to the value stored
in st1 or st2 (etc..). Instead the WHERE clause reads literally "WHERE
tbl_Reports. & st1 = True"
How can I get the VALUE within the variable to replace the " & st1" bit of
the clause.
ALSO, while I've got your attention (!!) -
In some of the queries I'll be inserting an "IN" statement. Is it OK to
include a WHERE IN phrase in cases where there is already a WHERE clause
preceding it? Or should I just put "AND IN" instead?
Sorry for the waffle.
Any help would be very much appreciated.
Kind regards,
Lee

 

[Jason Byrnes]

Sorry to keep bothering you guys but I really hope you can help me out yet
again before my hair has completely fallen out.
I've got over 20 queries that I want vb code to 'build' via SQL. The
queries are very similar in that they are counting the number of records
within a particular field. I'm trying to get the code to loop through each
query and to refer to each (different) table field name by storing it in a
variable.
Here's the code:

st1 = "name of table field 1"
st2 = "name of table field 2"
st3 = "name of table field 3"
st4 = "name of table field 4" ...and so on up to st22

intQueryRef = 1
intStrRef = 1

Do Until intQueryRef = 23
stDocName = "qry_Stats" & intQueryRef
Set loqd = CurrentDb.QueryDefs(stDocName)
stSQL = "SELECT Count(tbl_Reports.Number) AS CountOfNumber "
stSQL = stSQL & "FROM tbl_Reports LEFT JOIN qry_Entries ON
tbl_Reports.Number = qry_Entries.Number "
stSQL = stSQL & "WHERE tbl_Reports." & " & st" & intStrRef & " = True;"
loqd.SQL = stSQL
intQueryRef = intQueryRef + 1
intStrRef = intStrRef + 1
Loop

However, I can't seem to get the WHERE clause to refer to the value stored
in st1 or st2 (etc..). Instead the WHERE clause reads literally "WHERE
tbl_Reports. & st1 = True"
How can I get the VALUE within the variable to replace the " & st1" bit of
the clause.
ALSO, while I've got your attention (!!) -
In some of the queries I'll be inserting an "IN" statement. Is it OK to
include a WHERE IN phrase in cases where there is already a WHERE clause
preceding it? Or should I just put "AND IN" instead?
Sorry for the waffle.
Any help would be very much appreciated.
Kind regards,
Lee

change the syntaxt of the line that reads :
stSQL = stSQL & "WHERE tbl_Reports." & " & st" & intStrRef & " = True;"

to:
stSQL = stSQL & "WHERE tbl_Reports.st" & intStrRef & " = True;"

Sorry, I cant help the second part of your question.

Jason

 

[Guest]

Hi Lee,

I didn't review all of the code and what it is tring to do, but for your
specific question you should be able to do what you want to do by using an
array variable:

Dim st(1 to 22) as String

st(1) = "name of table field 1"
st(2) = etc

stSQL = stSQL & "WHERE tbl_Reports." & st(intStrRef) & " = True;"

By the way, you could also create a short loop to load all table field names
into the array if you wanted (air code)

Dim db as DAO.Database
Dim tbl as Table
Dim fld as Field
Dim intCounter as Integer

set db = CurrentDb
set tbl = db.TableDefs("YourTableName")
intCounter = 1
For Each fld in tbl.Fields
st(intCounter) = fld.Name
intCounter = intCounter + 1
Next

Of course, you would probably want to also check tbl.Fields.Count before the
loop and reDim st() if necessary to be more flexible.

Regarding your other question, you cannot have multiple WHERE clauses, so
you would need to add an AND or an OR, and then your table.field name and
then the IN().

HTH, Ted Allen

 

[MGFoster]

Sorry to keep bothering you guys but I really hope you can help me out yet
again before my hair has completely fallen out.
I've got over 20 queries that I want vb code to 'build' via SQL. The
queries are very similar in that they are counting the number of records
within a particular field. I'm trying to get the code to loop through each
query and to refer to each (different) table field name by storing it in a
variable.
Here's the code:

st1 = "name of table field 1"
st2 = "name of table field 2"
st3 = "name of table field 3"
st4 = "name of table field 4" ...and so on up to st22

intQueryRef = 1
intStrRef = 1

Do Until intQueryRef = 23
stDocName = "qry_Stats" & intQueryRef
Set loqd = CurrentDb.QueryDefs(stDocName)
stSQL = "SELECT Count(tbl_Reports.Number) AS CountOfNumber "
stSQL = stSQL & "FROM tbl_Reports LEFT JOIN qry_Entries ON
tbl_Reports.Number = qry_Entries.Number "
stSQL = stSQL & "WHERE tbl_Reports." & " & st" & intStrRef & " = True;"
loqd.SQL = stSQL
intQueryRef = intQueryRef + 1
intStrRef = intStrRef + 1
Loop

However, I can't seem to get the WHERE clause to refer to the value stored
in st1 or st2 (etc..). Instead the WHERE clause reads literally "WHERE
tbl_Reports. & st1 = True"
How can I get the VALUE within the variable to replace the " & st1" bit of
the clause.
ALSO, while I've got your attention (!!) -
In some of the queries I'll be inserting an "IN" statement. Is it OK to
include a WHERE IN phrase in cases where there is already a WHERE clause
preceding it? Or should I just put "AND IN" instead?
Sorry for the waffle.
Any help would be very much appreciated.
Kind regards,
Lee

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This:
stSQL = stSQL & "WHERE tbl_Reports." & " & st" & intStrRef & " = True;"

Should be like this:
stSQL = stSQL & "WHERE tbl_Reports." & st & intStrRef & " = True;"

You may wish to use an array rather than individual variables.

Dim st(1 to 22) As String
st(1) = "name of table field 1"
st(2) = "name of table field 2"
.... etc. ...

Then your WHERE clause would look like this:

stSQL = stSQL & "WHERE tbl_Reports." & st(intStrRef) & " = True;"

On using IN, just add AND column_name IN (...). There cannot be more
than one WHERE in a WHERE clause.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4U7IechKqOuFEgEQJuywCgoBh/7tUcI4HTKLIRBuqAoDURkXEAoLHI
GkM79gO8oo5cFDIrWJWMQZUY
=s2P6
-----END PGP SIGNATURE-----

 

[Guest]

Thanks so much for your helpful reply Ted.
I've never used an array before (as you've probably guessed!) so this seems
a good place to start as you say.
Best Regards,

Lee

 

[Guest]

My pleasure, glad it helped.

Thanks so much for your helpful reply Ted.
I've never used an array before (as you've probably guessed!) so this seems
a good place to start as you say.
Best Regards,

Lee