run queries with date parameters and storing results in new tables (BEST WAY)

[colmkav]

What is the best way to approach my problem:
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01. I also wish to use a date parameter which will usually be
the previous days date.
How best is it to do this? Currently the queries run fine on
themselves when I hardcode the date to =date() in the SQL queries but
when I try to use VBA and run the queries and return the results to a
new table using “SELECT * INTO…” and use parameters for the dates I
get overflow errors.
What’s the neatest way to solve this?
Colm

[Ivar]

When ever I have to use dates in SQL I Build a string and format any dates
so that the day and month cannot be confused. usually "DD/MMM/YYYY" and put
double quotes at each end of the formatted date.
EG:
SQL = SQL & "(FieldName = " & chr(34) & format(DateVariable,"DD/MMM/YYYY") &
chr(34) & ")"
I never seem to have a problem with this approach in VB, Never done it in
VBA.

[Deanna Earley]

Post by colmkav
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01. I also wish to use a date parameter which will usually be
the previous days date.
How best is it to do this? Currently the queries run fine on
themselves when I hardcode the date to =date() in the SQL queries

For filtering on dates, each database library has it's own format for
date literals.

MySQL: """" & Format$(Value, "yyyy-mm-dd hh:mm:ss") & """"
SQL server: "'" & Format$(Value, "yyyy-mm-dd hh:mm:ss") & "'"
Access: "#" & Format$(Value, "mm/dd/yyyy hh:mm:ss") & "#"

Access uses the same date literal format as VB6 and MUST be
month/day/year order.

I believe all of these can be limited to just the time or the date part
by removing the unneeded part from the format string.

DO NOT EVER store or pass a date around as a string.

Post by colmkav
but when I try to use VBA and run the queries and return the results
to a new table using “SELECT * INTO…” and use parameters for the
dates I get overflow errors.

Overflow errors on what?
Is everything Typed correctly as Date?

[colmkav]

Post by Deanna Earley

Post by colmkav
I want to run a series of Access SQL queries (currently 13) eg
qryRule01 and store the results in tables of the similar name eg
tblRule01. I also wish to use a date parameter which will usually be
the previous days date.
How best is it to do this? Currently the queries run fine on
themselves when I hardcode the date to =date() in the SQL queries

For filtering on dates, each database library has it's own format for
date literals.
MySQL: """" & Format$(Value, "yyyy-mm-dd hh:mm:ss") & """"
SQL server: "'" & Format$(Value, "yyyy-mm-dd hh:mm:ss") & "'"
Access: "#" & Format$(Value, "mm/dd/yyyy hh:mm:ss") & "#"
Access uses the same date literal format as VB6 and MUST be
month/day/year order.
I believe all of these can be limited to just the time or the date part
by removing the unneeded part from the format string.
DO NOT EVER store or pass a date around as a string.

Post by colmkav
but when I try to use VBA and run the queries and return the results
to a new table using “SELECT * INTO…” and use parameters for the
dates  I get overflow errors.

Overflow errors on what?
Is everything Typed correctly as Date?
--
i-Catcher Development Teamhttp://www.icode.co.uk/icatcher/
iCode Systems
(Replies direct to my email address will be ignored.
Please reply to the group.)

thanks for the replies. I think my issue is less to do with the dates
and more to do with the creating a new table to store the results.
Will post a better phrased questions following this.