The use of between

[Guest]

I have a crosstab query and was searching information beween 2 given dates
e.g., between 12/15/05 and 12/30/05.

I thought when one uses between it means e.g., the dates will equal
12/15/05 and 12/30/05 and anything that is betwen those 2 dates. but I was
puzzled when I noticed that it does not count the information that falls in
12/30/05 but if I use 12/31/05 which was a weekend date and nothing was done
in that date, then I get all the records Plus all the records that happened
in 12/30/05. Is that how between works? or am I missing something here?

 

[Dirk Goldgar]

I have a crosstab query and was searching information beween 2 given
dates e.g., between 12/15/05 and 12/30/05.

I thought when one uses between it means e.g., the dates will equal
12/15/05 and 12/30/05 and anything that is betwen those 2 dates. but
I was puzzled when I noticed that it does not count the information
that falls in 12/30/05 but if I use 12/31/05 which was a weekend date
and nothing was done in that date, then I get all the records Plus
all the records that happened in 12/30/05. Is that how between
works? or am I missing something here?

My guess is that your date fields have the time of day encoded in the
field along with the date. That will happen if you use the Now()
function instead of the Date() function to assign values to the field.

If this is the case, then a record for something that happened on
12/30/05 will actually store something like #12/30/2005 11:31 AM#. This
value is *later* than #12/30/2005#, which is equivalent to #12/30/2005
12:00 AM# (that is, the midnight that falls between 12/29 and 12/30).
Therefore, your record with a date value of, e.g., #12/30/2005 11:31 AM#
would be excluded by a WHERE clause that specifies BETWEEN #12/15/2005#
AND #12/30/2005#.

If the times actually don't matter in your database, use the Date()
function instead of Now() and run an update query to drop the time
portion from all existing records. Then you can use the simple BETWEEN
expression you're using now.

If the times *do* matter, then change your WHERE clause to say

BETWEEN #12/15/2005# AND #12/30/2005 11:59:59 PM#

or use a compound expression like this:

[DateField] >= #12/15/2005# And [DateField] < #12/30/2005#

 

[Guest]

Thanks alot, that was very informative!

I have a crosstab query and was searching information beween 2 given
dates e.g., between 12/15/05 and 12/30/05.

I thought when one uses between it means e.g., the dates will equal
12/15/05 and 12/30/05 and anything that is betwen those 2 dates. but
I was puzzled when I noticed that it does not count the information
that falls in 12/30/05 but if I use 12/31/05 which was a weekend date
and nothing was done in that date, then I get all the records Plus
all the records that happened in 12/30/05. Is that how between
works? or am I missing something here?

My guess is that your date fields have the time of day encoded in the
field along with the date. That will happen if you use the Now()
function instead of the Date() function to assign values to the field.

If this is the case, then a record for something that happened on
12/30/05 will actually store something like #12/30/2005 11:31 AM#. This
value is *later* than #12/30/2005#, which is equivalent to #12/30/2005
12:00 AM# (that is, the midnight that falls between 12/29 and 12/30).
Therefore, your record with a date value of, e.g., #12/30/2005 11:31 AM#
would be excluded by a WHERE clause that specifies BETWEEN #12/15/2005#
AND #12/30/2005#.

If the times actually don't matter in your database, use the Date()
function instead of Now() and run an update query to drop the time
portion from all existing records. Then you can use the simple BETWEEN
expression you're using now.

If the times *do* matter, then change your WHERE clause to say

BETWEEN #12/15/2005# AND #12/30/2005 11:59:59 PM#

or use a compound expression like this:

[DateField] >= #12/15/2005# And [DateField] < #12/30/2005#

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

in my parameter, since I used now( ) in my date field as I need to capture
both date and time, How would I do it in my parameter?

I have a form that one has to enter begin date and End date...how will go
about the crosstabquery that pulls that information?

I have a crosstab query and was searching information beween 2 given
dates e.g., between 12/15/05 and 12/30/05.

I thought when one uses between it means e.g., the dates will equal
12/15/05 and 12/30/05 and anything that is betwen those 2 dates. but
I was puzzled when I noticed that it does not count the information
that falls in 12/30/05 but if I use 12/31/05 which was a weekend date
and nothing was done in that date, then I get all the records Plus
all the records that happened in 12/30/05. Is that how between
works? or am I missing something here?

My guess is that your date fields have the time of day encoded in the
field along with the date. That will happen if you use the Now()
function instead of the Date() function to assign values to the field.

If this is the case, then a record for something that happened on
12/30/05 will actually store something like #12/30/2005 11:31 AM#. This
value is *later* than #12/30/2005#, which is equivalent to #12/30/2005
12:00 AM# (that is, the midnight that falls between 12/29 and 12/30).
Therefore, your record with a date value of, e.g., #12/30/2005 11:31 AM#
would be excluded by a WHERE clause that specifies BETWEEN #12/15/2005#
AND #12/30/2005#.

If the times actually don't matter in your database, use the Date()
function instead of Now() and run an update query to drop the time
portion from all existing records. Then you can use the simple BETWEEN
expression you're using now.

If the times *do* matter, then change your WHERE clause to say

BETWEEN #12/15/2005# AND #12/30/2005 11:59:59 PM#

or use a compound expression like this:

[DateField] >= #12/15/2005# And [DateField] < #12/30/2005#

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

in my parameter, since I used now( ) in my date field as I need to
capture both date and time, How would I do it in my parameter?

I have a form that one has to enter begin date and End date...how
will go about the crosstabquery that pulls that information?

Try this as the WHERE clause of your query:

WHERE [DateField] >= [Forms]![YourFormName]![BeginDate]
And [DateField] < [Forms]![YourFormName]![EndDate] + 1

 

[Guest]

Thanks alot, that worked very well...

in my parameter, since I used now( ) in my date field as I need to
capture both date and time, How would I do it in my parameter?

I have a form that one has to enter begin date and End date...how
will go about the crosstabquery that pulls that information?

Try this as the WHERE clause of your query:

WHERE [DateField] >= [Forms]![YourFormName]![BeginDate]
And [DateField] < [Forms]![YourFormName]![EndDate] + 1

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)