How do i create a form that pulls data back between two dates?

S

Sellma

I have a database that contains yearly data. Every month I need the last
months worth of data, I do this by amending the download query's data range,
for example. 'Between #01/07/2005# And #31/07/2005#'
How do this using a 'Form'?
I would like a form that has two inputs, one the 'Start date' and one for
'End date'.

Does any one have any ideas? My attempts with the combo and List wizards
have only allowed the start date to be used.

Many thanks
Sellma
 
R

Rick Brandt

Sellma said:
I have a database that contains yearly data. Every month I need the
last months worth of data, I do this by amending the download query's
data range, for example. 'Between #01/07/2005# And #31/07/2005#'
How do this using a 'Form'?
I would like a form that has two inputs, one the 'Start date' and one
for 'End date'.

Does any one have any ideas? My attempts with the combo and List
wizards have only allowed the start date to be used.

Many thanks
Sellma
Click to expand...

If you always want the previous month then you don't need to enter dates at all.
Just use a Criteria like...

BETWEEN DateSerial(Year(Date(), Month(Date())-1,1) AND DateSerial(Year(Date()),
Month(Date()),0)

If you do want to see other months and want to use a form then...

BETWEEN Forms!NameOfForm!StartDate AND Forms!NameOfForm!EndDate

If your DateTime field includes times other than midnight then you need to add a
day to the end date.

BETWEEN Forms!NameOfForm!StartDate AND DateAdd("d", 1, Forms!NameOfForm!EndDate)
 
D

Douglas J Steele

In addition to Rick's comment, be aware that #01/07/2005# will always be
treated as 07 Jan, 2005, regardless of your regional settings. I assume this
isn't what you want!

You might find it useful to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I had in my September
2003 "Access Answers" column for Pinnacle Publication's "Smart Access"
newsletter. The column and accompanying database can be downloaded (for
free) at http://www.accessmvp.com/djsteele/SmartAccess.html
 
Top