Date/Time won't show items on first day in query

G

g2v

I have a database that is tracking meals in our cafeteria. I have it track
when a card is scanned and it records the card number, the type of meal and
the DATEADDED. Date Added is a date/time field that shows: 8/4/2008 4:59:43
PM. It's a field I created in the table that defaults to Now() so it
automatically records the current date/time to know exactly when they swipped
it (if we have a question about when the meal was scanned).

I have the cards assigned to people when they take a course. Then when I
run a roster of current students for that course, it will show all their
meals and a total for the course. I noticed it was NOT recording the first
day of meals when I used the following query below.

All is working great, but when I try to access my START DATE and END DATE
fields for that course and have it only show cards that were scanned between
10/1/08 and 10/15/08 for a particular class (again, automatically using the
start/end date to limit errors), it doesn't show any swipes for the first day.

I'm trying to use the following expression on DATE ADDED in my query:
Between [qryHistoryStudentCourses].[startDate] And
[qryHistoryStudentCourses].[endDate]

Again it's not showing meals for the first day. I had it sort of working
before where it was a manual date query and I'd have the person type on the
screen the DAY BEFORE the start date and the DAY AFTER the end date to try to
encompass all meal histories.

We reissue those cards to various classes. So it's possible that two people
could have card number S10 on back to back days from one class to another.
I'd really like it to track meals by just clicking on a course and pulling
the start/end date from their. I'm assuming it has something to do with the
time, maybe with midnight on the first day, etc.

startDate
endDate
are both just date/time fields with each course.

Thanks to all for any ideas, inputs!
Tony
 
K

KARL DEWEY

DateTime has a decimal component for time so you need to strip that off.
Open query in design view and add a calculated fied like --
Date_Only: DateValue([DATEADDED])
Use your between on this field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top