between [EnterStartDate] and [EnterStopDate]

[Vadimbar]

I have used this function in my Query and it works great. But I found that when I try to access the same day i.e between [1/1/03] and [1/1/03]
I get empty results. Oh, yes I know that 1/1/03 does exist.
What am I missing?

Thank you,
VADIMBAR

 

[fredg]

I have used this function in my Query and it works great. But I found that when I try to access the same day i.e between [1/1/03] and [1/1/03]
I get empty results. Oh, yes I know that 1/1/03 does exist.
What am I missing?

Thank you,
VADIMBAR

If you are storing the time value along with the date, i.e. you used
Now() to enter the data, then a query criteria for the end date of
7/14/2004 will NOT return records of 7/14/2004 unless their time value
is exactly midnight (the default time value in a Date field).
(7/14/2004 16:25:00 is later than 7/14/2004 00:00:00)

You can work around this.
You can simply manually add one day to the end date, i.e. enter
1/2/2004 when you only want records up to 1/1/2004
Or ...

Change the query parameter to:
between [Enter Start Date] and [Enter End Date] +1

You must then click on the Query Menu button and set the parameter
dialog to
[Enter Start Date] DateTime
[Enter End Date] DateTime

Now you can enter the exact ending date and Access will increase it by
one day.

 

[Vadimbar]

Thank you Fred thats perfect!
VADIMBAR...

I have used this function in my Query and it works great. But I found that when I try to access the same day i.e between [1/1/03] and [1/1/03]
I get empty results. Oh, yes I know that 1/1/03 does exist.
What am I missing?

Thank you,
VADIMBAR

If you are storing the time value along with the date, i.e. you used
Now() to enter the data, then a query criteria for the end date of
7/14/2004 will NOT return records of 7/14/2004 unless their time value
is exactly midnight (the default time value in a Date field).
(7/14/2004 16:25:00 is later than 7/14/2004 00:00:00)

You can work around this.
You can simply manually add one day to the end date, i.e. enter
1/2/2004 when you only want records up to 1/1/2004
Or ...

Change the query parameter to:
between [Enter Start Date] and [Enter End Date] +1

You must then click on the Query Menu button and set the parameter
dialog to
[Enter Start Date] DateTime
[Enter End Date] DateTime

Now you can enter the exact ending date and Access will increase it by
one day.

 

[Michelle T.]

This works great except when you have both dates with and without the time in
the same field. If I enter 8/31/04 for my parameter for both start and end
dates I get 9/1/04 also for those records that don't have a time after the
date portion. I am using Access as a front end for Oracle and the Oracle
database is set up this way and I can't change it as I am not the admin. I
have tried formatting the field in the query but my query doesn't run when I
do this I get an odbc call failure. Any suggestions other than begging and
pleading for the Oracle dba to put their dates and times in seperate fields?

Thank you Fred thats perfect!
VADIMBAR...

I have used this function in my Query and it works great. But I found that when I try to access the same day i.e between [1/1/03] and [1/1/03]
I get empty results. Oh, yes I know that 1/1/03 does exist.
What am I missing?

Thank you,
VADIMBAR

If you are storing the time value along with the date, i.e. you used
Now() to enter the data, then a query criteria for the end date of
7/14/2004 will NOT return records of 7/14/2004 unless their time value
is exactly midnight (the default time value in a Date field).
(7/14/2004 16:25:00 is later than 7/14/2004 00:00:00)

You can work around this.
You can simply manually add one day to the end date, i.e. enter
1/2/2004 when you only want records up to 1/1/2004
Or ...

Change the query parameter to:
between [Enter Start Date] and [Enter End Date] +1

You must then click on the Query Menu button and set the parameter
dialog to
[Enter Start Date] DateTime
[Enter End Date] DateTime

Now you can enter the exact ending date and Access will increase it by
one day.