Passing Variables from VBA to Access Queries

[Drayton]

Informational post. Hope someone finds this helpful.

I have been working on passing variables set within a VBA module over to an
Access query for use as criteria. This has proved problematic, but do-able.
This might be the long way around, but it works as intended.

Start by the usual declarations, and setting the values for these variables.
like so....
Dim MYDAY, MYMONTH, MYYEAR, MYTODAY As Date
Dim QDATE As String

MYTODAY = Date 'date
MYDAY = Day(MYTODAY) 'date
MYMONTH = Month(MYTODAY) 'date
MYYEAR = Year(MYTODAY) 'date
In this case, I build a string....
QDATE = "'" & MYMONTH & "/" & MYDAY & "/" & MYYEAR & "'"
'PASSED VARIABLE TO EXT QUERIES
then use a simple SQL statement to update a field in a table to this
variable's value like so.......
DoCmd.RunSQL "UPDATE TBLREPORT_DATE SET
TBLREPORT_DATE.REPORT_DATE = " & QDATE & ""
then just bring that table into the design grid of your query and , in this
case, create a join from a date field in the source table over to the field
"report_date"
that is in a very simple table that has one field:"report_date"
Now, only the records that have equal values in both tables will output.

I am probably using a hammer to drive a thumb tack but it works....
Nice thing about this: I do not have to use a form to hold this criteria,
and it is available to ANY query within the Access db.
This is a very simple example of what I am currently using, and with a few
mods, can be expanded to pass as many variables as needed.

Hope this helps.

Comments and/or suggestions are welcome.

DT