Query Criteria

[Guest]

How do you apply VB generated criteria to a query? Could someone give me the
details on how, in vb, to write a select statement where the criteria
variable can be dropped in and that recordset can then be used for a query.

I need to generate a query prefiltered by date and status, so I can count
the items a person has open. If Bill has ten items, six are open but only
four are in June, if the date and status fields are in the same query I get
results like this:
Bill 6/5/06 3 Open
Bill 6/7/06 1 Open
What I need is his open total for June: (Bill 4), but the count works off
the different dates. So, my thinking is if I have a query that is filtered by
the criteria, and it is the recordsource for the second query, (don't know
how to do this either), then the count will work properly. Any help with this
would be greatly appreciated

 

[MGFoster]

How do you apply VB generated criteria to a query? Could someone give me the
details on how, in vb, to write a select statement where the criteria
variable can be dropped in and that recordset can then be used for a query.

I need to generate a query prefiltered by date and status, so I can count
the items a person has open. If Bill has ten items, six are open but only
four are in June, if the date and status fields are in the same query I get
results like this:
Bill 6/5/06 3 Open
Bill 6/7/06 1 Open
What I need is his open total for June: (Bill 4), but the count works off
the different dates. So, my thinking is if I have a query that is filtered by
the criteria, and it is the recordsource for the second query, (don't know
how to do this either), then the count will work properly. Any help with this
would be greatly appreciated

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

To use VBA to run a query: first set up a QueryDef like the following.

PARAMETERS [Begin Date] Date, [End Date] Date;
SELECT sales_rep, Month(sales_date) As Mnth, Count(*) As StillOpen
FROM table_name
WHERE status = "Open"
AND sales_date BETWEEN [Begin Date] And [End Date]
GROUP BY sales_rep, Month(sales_date)

Get the result set in a DAO Recordset like this:

dim db as dao.database, rs as dao.recordset, qd as dao.querydef

set db = currentdb
set qd = db.querydefs("the query's name")
qd![Begin Date] = #6/1/2006#
qd![End Date] = #6/30/2006#

set rs = qd.OpenRecordset()

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

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

iQA/AwUBRK4YGIechKqOuFEgEQIeHwCeJg0b1roPwd+fYEJcXxwyP2scGKgAoIOd
PrDGi8tS463uYXwg/pu02Z6M
=2Dct
-----END PGP SIGNATURE-----

 

[John Spencer]

If what you need is exactly what you have posted, then you can do this all
in one query. You apparently already have a query that will give you the
results you want. You just need to modify it. Change Group By to Where
under you date field.

You could also use the dCount function in VBA

DCount("*","Your Table Name","PersonName =""Bill"" And DateField

=#2006-06-01# And #2006-06-30#")

OF course, hard coding the values is probably NOT what you want.

The SQL text of a query might look like

SELECT PersonName
, Format(ActionDate,"yyyymm") as YearMonth
, Count(PersonName) as OpenItems
FROM YourTable
WHERE Status = "Open"
GROUP BY PersonName, Format(ActionDate,"yyyymm")