Filters not working?

  • Thread starter Thread starter Caroline
  • Start date Start date
C

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
 
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.
 
You can use a calculated date --
MyDate: DateValue([Date Purchased])

Dale_Fye via AccessMonster.com said:
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
Click to expand...
Click to expand...
 
Back
Top