Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1199

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

Show all headers | View raw

On Jun 7, 4:48 pm, Deanna Earley <dee.ear...@icode.co.uk> wrote:
> On 07/06/2012 14:35, colmkav wrote:
>
> > 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
>
> 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.
>
> > 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?
>
> --
> Deanna Earley (dee.ear...@icode.co.uk)
> 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.

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Find similar

Thread

run queries with date parameters and storing results in new tables (BEST WAY) colmkav <colmjkav@yahoo.co.uk> - 2012-06-07 06:35 -0700
  Re: run queries with date parameters and storing results in new tables (BEST WAY) "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-06-07 15:13 +0100
  Re: run queries with date parameters and storing results in new tables (BEST WAY) Deanna Earley <dee.earley@icode.co.uk> - 2012-06-07 15:48 +0100
    Re: run queries with date parameters and storing results in new tables (BEST WAY) colmkav <colmjkav@yahoo.co.uk> - 2012-06-08 00:40 -0700

csiph-web