Another query based on date. (ACC97)

[AccUser]

I have a macro that I run weekly on Fridays through use of the Windows Task Scheduler. It works fine, except for one thing. It is date-based. A table hold certain info, including dates in this format YYYYMM.

My query has the start with 'YYYYMM' and end with 'YYYYMM'.

The parameters are always based as follows:

If the current month is may, the first parameter should be '200206'. Accordingly, the last parameter should be 12 months out or '200305'.

If I remember to go into the query and change the hard-coded date, no problem. It just so happened that 2 weeks ago I forgot to change it on May 1, 2002, Friday. Sure enough, it ran with the parameters of '200205' and '200304'.

My question: How can I use the computer's system date to set these parameters automatically?

Thanks in advance.

 

[petermeachem]

Start date is
format(now,"yyyymm&quot

End date is
format(dateadd("m",11,now),"yyyymm&quot Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[AccUser]

Is this code to be used in Visual Basic or is there a place where I enter this in my query?

For example, if I right-click on the grey area in the query window, one of my options is "parameters". How would I enter in this area?

Or, do I need to create a module for this (I am weak in this area)?

Thank you in advance.

 

[AccUser]

I figured it out. In the grid portion, I replaced the hard-coded dates and entered the format parameters. It works with a minor hitch.

The starting date should be the following month of the current month, which is why I had previously posted if current month is May, the starting date should reflect a June date. And is it seems that the ending date ends in April, where it should end in May of the following year.

I changed the parameters as follows:

Between Format(DateAdd("m",1,Now()),"yyyymm&quot And Format(DateAdd("m",12,Now()),"yyyymm&quot

Now, the starting date will be current month plus one and the ending date plus 12.

Now, the query results correctly gives data starting in June and ending in May.

Thank for input. It was most invaluable.

AccUser!