QUERY DATE RANGE

  • Thread starter Thread starter hotrod1952
  • Start date Start date
H

hotrod1952

I AM A NEWBIE: I am writing a query in Access97 to pull data in between two
dates. The expression I am using is: Between [ #"START DATE dd/mm/yy"# ]
And [#"END DATE dd/mm/yy" #] in the critera field. The result is pulling all
dates from START DATE and only the first entry in the END DATE. I want to
include all entries for the END DATE.
 
Presumably this expression is in the Criteria row of query design,
underneath a field that is a Date/Time field. If this field contains a time
component, the final day (i.e. the day that matches your END DATE) will not
be included, because the time is after the beginning of that day. To work
around that, ask for less than the next day.

I'm really not too sure about the # and quote marks in the parameter names.
Try:
= [START DATE] AND < DateAdd("d", 1, [END DATE])
Click to expand...

To ensure Access understands them, declare your parameters. In query design
view, choose Parameters on the Query menu. Enter 2 rows in the dialog: the
same name as you used in the Criteria row, and Date for the type in the 2nd
column, i.e.:
START DATE Date/Time
END DATE Date/Time

Since you live in a d/m/y country, you might also get some help to ensure
Access understands your dates correctly from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hotrod1952 said:
I AM A NEWBIE: I am writing a query in Access97 to pull data in between
two
dates. The expression I am using is: Between [ #"START DATE dd/mm/yy"# ]
And [#"END DATE dd/mm/yy" #] in the critera field. The result is pulling
all
dates from START DATE and only the first entry in the END DATE. I want
to
include all entries for the END DATE.
Click to expand...
 
hotrod1952 said:
I AM A NEWBIE: I am writing a query in Access97 to pull data in
between two dates. The expression I am using is: Between [ #"START
DATE dd/mm/yy"# ] And [#"END DATE dd/mm/yy" #] in the critera
field. The result is pulling all dates from START DATE and only the
first entry in the END DATE. I want to include all entries for the
END DATE.
Click to expand...

If your table data Date fields actually include time values other than midnight
then your End Date parameter will only include those rows where the time is
exactly midnight if the user only enters a date into the parameter.

The usual solution is to add one extra day to the end date, but if you do have
some records without time then you might get records from that extra day. To
avoid that you can use >= and < instead of BETWEEN.

FieldName >= [ #"START DATE dd/mm/yy"# ]
And FieldName < DateAdd("d", 1, [#"END DATE dd/mm/yy" #])
 
You can do this by putting in on end date 1-13-06 11:59:59 pm this should
take care of that day. It sounds like you field includes both date and time
 
I had already declared the parameters. Your solution worked perfectly. Thanks
Everyone!


Allen said:
Presumably this expression is in the Criteria row of query design,
underneath a field that is a Date/Time field. If this field contains a time
component, the final day (i.e. the day that matches your END DATE) will not
be included, because the time is after the beginning of that day. To work
around that, ask for less than the next day.

I'm really not too sure about the # and quote marks in the parameter names.
Try:
= [START DATE] AND < DateAdd("d", 1, [END DATE])
Click to expand...

To ensure Access understands them, declare your parameters. In query design
view, choose Parameters on the Query menu. Enter 2 rows in the dialog: the
same name as you used in the Criteria row, and Date for the type in the 2nd
column, i.e.:
START DATE Date/Time
END DATE Date/Time

Since you live in a d/m/y country, you might also get some help to ensure
Access understands your dates correctly from this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
I AM A NEWBIE: I am writing a query in Access97 to pull data in between
two
Click to expand...
[quoted text clipped - 4 lines]
to
include all entries for the END DATE.
Click to expand...
Click to expand...
 
Back
Top