Date Between Problem after changing table

G

Grizz

There was a mistake on one table that had 2 fields named DATE and TIME, Date
was using Date() and Time was using Now(). I changed the fields to one
field, named CallInDateTime formatted "mm/dd/yyyy h:nn AM/PM" on the
form it is =Now() it works slick for the form and couple of reports. My
problem is I think this change messed up one Query where it was a Date
Between criteria. Could this between selection be asking for the time too?
If it does How to seperate just the date? There is about that many spaces in
between each of the formats too. Or could it be in the criteria code.
WHERE (((CallInTbl.CallInDateTime) Between [Enter First Date selection] And
[Enter Last Date Selection] And (CallInTbl.CallInDateTime)="!NULL"));
Thank You for your help on this
 
A

Allen Browne

Internally, a date/time field in Access is treated as a real number, where
the integer part represents the date, and the fractional part the time (e.g.
0.25 = 6am, i.e. one quarter of a day.) This does have consequences for your
criteria. If today is 39800, and you ask for dates that match 39800, then
Now() will not meet that criteria because it is larger (e.g. 39800.75 at
6pm.)

The solution is to ask for values that are less than the next day:
WHERE (CallInTbl.CallInDateTime >= [Enter First Date selection])
AND (CallInTbl.CallInDateTime < [Enter Last Date Selection] +1)

The other possibility is that the data type of the parameters may not be
understood correctly. To correct this, declare your parameters. Choose
Parameters on the Query menu. Access opens a dialog. Enter 2 rows, like
this:
[Enter First Date selection] Date/Time
[Enter Last Date Selection] Date/Time
Both tricks together should solve the problem.

I'm not clear what you intended by comparing what I assume is a Date/Time
field to the literal text:
!NULL
If you were trying to say:
AND (CallInTbl.CallInDateTime Is Not Null)
that would be redundant, as using the criteria above excludes nulls anyway.

HTH.
 
G

Grizz

^^^^^ High Five Allen ^^^^ Thanks again for yor help!, I might be asking
another one as I am still on the same process from my first post with your
help. I understand now, that using the Not Null was not the correct thing to
do, but after I put it in, I didn't get another blank report. I was thinking
that blank pages should have been filtered out.
 

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