queries picking up and calculating dates 1

[donnycarr]

I have set up 12 or so queries. Within these are some date criteria whereby the query picks up a date from a form ( through a text box on the form, formatted to Date/Time and with an Input Mask where the user inputs a certain date).
I now want the query to work out the 4 week period before this date and use that criteria to select records from the database.

Also…

When my macro runs these 12 queries it asks the user to enter the date into the input mask for every query, Is it possible to just enter the date once for all 12?

Any help suggestions would be much appreciated,

..Donny

 

[Sawatzky]

dear donny,

it is possible to let the query pick the data directly from the form.
edit your query , right click on the criterion , select build.. , and reference it to the form.

to have your query selected a certain range try the

between (expression ) and (expression) function

where the first expression could be the usage of the dateadd function to have the time period you want.
like this

between DateAdd("ww", -4,Formulare![Formular2]![Text0] ) and Formulare![Formular2]![Text0]

HTH
regards Astrid

 

[donnycarr]

Thanks Astrid that has worked.

However, its gets more complicated.
I now need to run a query to select records for the same 4 week period the previous year. Still using the date entered by the user as a guide.

I was thinking I might be able to create some forms which could calculate all these different dates from each other and then get the queries to pick up the dates from there, but to be honest I don't know the best way.

If you can help me out on this one then you are a star..

 

[Sawatzky]

dear donnycarr ,

pardon me for the long time passed.

the dateadd function determines in the string param "ww" what time space is used so if you combine 2 of them one adding the weeks and then 1 adding a year. you should get the results wanted.


regards Astrid

 

[donnycarr]

I have TRIED to set up a form with various text boxes. I just want to type in a date into these boxes (about 6 of them)
I then want my queries to refer to these so they can select, 'Between this date and that date".

Every time I run the queires they ask me to input the date again, but they are in the form?

This is really simple but its not working.

Please help!

Cheers,

Donny

 

[tempclerk]

Make sure in each query you are refering to the form fields correctly (check spelling). The syntax is Forms![Formname]![controlname]. If the query doesn't recognize the reference (or doesn't find the form open) it will ask for it as a parameter.

 

[donnycarr]

Thanks for that advice.. I tried everything you suggested. For some reason it doesn't want to pick up the dates I have entered into the text boxes on the form. All my references appear to be correct. My query criteria reads:
[forms]![date]![tb1]
'date' being the name of my form and 'tb1' being the name of my text box I want the query to pick up the date from.
..So I have my form open, I type the date 08/09/01 into tb1(text box 1) and then run my query. But, it comes up with no records. If I close the form and run the query again, its asks for a parameter for tb1. If you enter the same date again, it works, it comes up with the records I need.
Can you shed any light. I think it's got something to do with my form. In design view the text box says unbound. In form view its blank until you type in the date (I have an input mask - 00/00/00;0)

If ANYONE can help me with this it would be much appreciated.

....Donny

 

[Sawatzky]

dear donnycarr,

did you try to explicitely convert the data from the form into date format.

between DateAdd("ww", -4,Formulare![Formular2]![Text0] ) and cdate(Formulare![Formular2]![Text0])

hth
regards astrid

 

[tempclerk]

Try getting rid of the input mask, too. If you just enter 8/9 into a date field it will be interpretted as 08/09/01 (current year).