A question about query parameters.
We are working on data which requires queries to pick rows between specific dates. These are always of the form 1/11/year and 31/10/year+1, where the year changes each time new data is processed. The year is always post 2000 i.e. there are no 1900 dates
If the query is set up using ‘Between [Start Date] and [End Date]’, then the entire date strings have to be input every time.
Is there are way whereby just a year could be entered and the strings “1/11/year” and “31/10/year+1”constructed for use by the query? If so, how?
Thanks
Silverback
Constructing query parameters
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Constructing query parameters
You can use
Between DateSerial([Enter Year],11,1) And DateSerial([Enter Year]+1,10,31)
or if you use a form frmParameter with a text box txtYear to enter the year:
Between DateSerial([Forms]![frmParameter]![txtYear],11,1) And DateSerial([Forms]![frmParameter]![txtYear]+1,10,31)
Between DateSerial([Enter Year],11,1) And DateSerial([Enter Year]+1,10,31)
or if you use a form frmParameter with a text box txtYear to enter the year:
Between DateSerial([Forms]![frmParameter]![txtYear],11,1) And DateSerial([Forms]![frmParameter]![txtYear]+1,10,31)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 776
- Joined: 29 Jan 2010, 13:30
Re: Constructing query parameters
This is wonderful and is working exactly how we wanted; thank you!
One question : Why doesn't Access prompt for year a second time? (I'm glad it doesn't)
Silverback
One question : Why doesn't Access prompt for year a second time? (I'm glad it doesn't)
Silverback
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Constructing query parameters
It recognizes that the second parameter is identical to the first one.
Best wishes,
Hans
Hans