Filters not working?

[Caroline]

I have a query that tells me the student ID, the ID of the course purchased,
the price, and the date purchased. If I filter this query by clicking on all
dates available in June, I get different results than when I click on "date
filters: this month". "This month" gives me 4 extra records that do not show
when I click on each single date in June. For example, two records indicate
6/2, but only one of them shows up on the filter where I click "6/2", but
both of them show up if I filter by "This month". Any idea why?
Thank you,
Caroline

 

[KARL DEWEY]

Do you have time with those records that do not show when selecting the
actual date?
You do know that time is recorded as a decimal fraction and therefore a date
with time is a larger number than just the date alone.

 

[Dale_Fye via AccessMonster.com]

Since we don't know what you are using to filter with, this becomes a bit
difficult. Whatever the method, my guess is that the query is ignoring time
values that may be imbedded in your [Date Purchased] field (assumes that
field might be populated using the Now() function rather than the Date()
function.

if the filter generates a where clause that looks like:

WHERE [Date Purchased] IN (#6/2/2009#, #6/3/2009#)

then if [Date Purchased] is #6/2/2009 1:00:00 PM# then this record would not
be included in the query results. Likewise, if the criteria reads something
like:

WHERE [Date Purchased] BETWEEN #6/2/2009# and #6/3/2009#

then it would return records starting at #6/2/2009 0:00:00# all the way
through the 2nd and stop at records including those where the [Date Purchased]
is #6/3/2009 00:00:00#, but would not include any record that occured later
in the day on #6/3/2009#.

I generally try to use the DateValue() function to strip off the time portion
of the [Date Purchased].

HTH
Dale

 

[Caroline]

Yes, that must be it. How can I remove it, can I do it by changing the format?

 

[KARL DEWEY]

You can use a calculated date --
MyDate: DateValue([Date Purchased])

Since we don't know what you are using to filter with, this becomes a bit
difficult. Whatever the method, my guess is that the query is ignoring time
values that may be imbedded in your [Date Purchased] field (assumes that
field might be populated using the Now() function rather than the Date()
function.

if the filter generates a where clause that looks like:

WHERE [Date Purchased] IN (#6/2/2009#, #6/3/2009#)

then if [Date Purchased] is #6/2/2009 1:00:00 PM# then this record would not
be included in the query results. Likewise, if the criteria reads something
like:

WHERE [Date Purchased] BETWEEN #6/2/2009# and #6/3/2009#

then it would return records starting at #6/2/2009 0:00:00# all the way
through the 2nd and stop at records including those where the [Date Purchased]
is #6/3/2009 00:00:00#, but would not include any record that occured later
in the day on #6/3/2009#.

I generally try to use the DateValue() function to strip off the time portion
of the [Date Purchased].

HTH
Dale

I have a query that tells me the student ID, the ID of the course purchased,
the price, and the date purchased. If I filter this query by clicking on all
dates available in June, I get different results than when I click on "date
filters: this month". "This month" gives me 4 extra records that do not show
when I click on each single date in June. For example, two records indicate
6/2, but only one of them shows up on the filter where I click "6/2", but
both of them show up if I filter by "This month". Any idea why?
Thank you,
Caroline