qry not showing all records

  • Thread starter Thread starter Nydia
  • Start date Start date
N

Nydia

I have a database that keeps track of clients and the
meetings that they go to, with progress notes. each time
a client goes to a meeting there are 7 different topics to
discuss (in a meeting more than 1 topic can be talked
about). When I run a query, in the date field I use
between [start date] and [end date]. For November I used
between 11/01/03 and 11/10/03, I know that there is 2
records for that date range, but only 1 is showing. there
is a record with 11/10/03 date, but it won't show. If i
use between 11/01/03 and 11/11/03, then it will show me
both records.

why doesn't it show the 11/10/03 record if i use between
11/01/03 and 11/10/03? How do i fix it so it will show me
all the records between certain dates??
 
Dear Nydia:

One likely cause of the problem is that, not surprisingly, a DateTime
column contains both a date and a time. Thus, 11/10/03 2:30:07 PM
does NOT fall "BETWEEN #11/01/03# AND #11/10/03#" because the date as
entered "11/10/03" is interpreted as "the first millisecond of
November 10, 2003". So, if the data entry for your table is set up to
allow a time to be entered with a time not zero, then you can see that
the query is really doing exactly what you told it. No surprise
there. Computer's aren't nearly as smart as humans, but they're
certainly methodical! In fact, being smart is one of the reasons we
so easily overlook such simple facts.

If this is the cause of your problem, there are several solutions.
One would be just to recognize that, if you want to query through the
end of the day on November 10th, you need to enter a time like
23:59:59.99 or just use November 11th. Another option is to change
the date/time entered to be explicitly at 23:59:59.99. Another is to
consider only the date portion from the field, effectively setting it
to 0:00:00.00 so it works with the date entered and effectively
considers the WHOLE day of November 10th instead of just the first
millisecond.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top