Between dates in a parameter query

[creole]

Hi,

I'm trying to achieve the effect of "Between #01/08/2004# And
#07/08/2004#" using parameter queries. I've tried using ">[After
date:] And <[Before what date:]" but i want to specify the first date
first and the method i've tried also excludes the dates i type, so you
have to type the date before... anyone?

 

[fredg]

Hi,

I'm trying to achieve the effect of "Between #01/08/2004# And
#07/08/2004#" using parameter queries. I've tried using ">[After
date:] And <[Before what date:]" but i want to specify the first date
first and the method i've tried also excludes the dates i type, so you
have to type the date before... anyone?

Between [Start Date] and [End Date]
will permit the user to enter the dates wanted.

You are not getting the first and last days because your criteria
doesn't as for them.

[After date:] And <[Before what date:]

You would want to use

=[After date:] And < = [Before what date:]

If, in using Between [Start Date] and [End Date] , you don't get the
final day, it is probably because your date field is storing a time
value as well as a date value.

Entering #07/08/2004# as an [End Date] will return records up to
Midnight of that date, and so no records appear to be returned (unless
you do actually have a date and time value of exactly midnight.

Either update your table to remove the time value,
or manually enter one day later to the wanted period,
or use:

Between [Start Date] and ([End Date]+1)

In which case you must enter the [Start Date] and [End Date] as
Date/Time parameters in the Query Parameter dialog box:
In Query Design View, click Query + Parameter

 

[LeAnne]

Hi,

Huh? You're complicating this unnecessarily, methinks. Try

Between [After this Date:] AND [Before this Date:] 'Substitute whatever
text you like in the brackets

Not sure what you mean by "specify the first date first" - the
BETWEEN...AND operator will display all records that match or fall
between the dates you specified as parameters, whether you put the
earliest date first or second. To put it another way, Access doesn't
care whether you enter "1/1/1999" at the first prompt and "12/31/1999"
at the second prompt, or the other way around...you'll still see all
records from the earlier date to the later date.

PS. This assumes that the field in the query is a date/time type, and
that the dates you enter as parameters are valid dates.

hth,

LeAnne

 

[John Vinson]

Hi,

I'm trying to achieve the effect of "Between #01/08/2004# And
#07/08/2004#" using parameter queries. I've tried using ">[After
date:] And <[Before what date:]" but i want to specify the first date
first and the method i've tried also excludes the dates i type, so you
have to type the date before... anyone?

Short answer:

BETWEEN [after date:] AND [Before what date:]

This will still miss some items *if* you have a Time portion stored in
the date - #10/15/2004 11:31:32# is in fact after #10/15/2004#, by
eleven and a half hours or so.

More robust:

= Format(CDate([Enter start date:]), "\#mm\/dd\/yyyy\#") AND < Format(CDate([Enter end date:]) + 1, "\#mm\/dd\/yyyy\#")

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps