Date Between Problem after changing table

  • Thread starter Thread starter Grizz
  • Start date Start date
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
 
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.
 
^^^^^ 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.
 
Back
Top