Dates with Hidden Time Stamp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble comparing two dates in a table because some of the values
have a time included. I see it when the cursor is put in the cell.

My query is selecting where Date1 is not equal to Date2, but is returning
some rows where the dates are equal.

What do I do?

Thanks!
 
Use DateValue to extract just the date value and then do the comparison.

TestDateValue: DateValue([FieldName1])
Criteria: =DateValue([Field2])
 
KADL said:
I am having trouble comparing two dates in a table because some of the values
have a time included. I see it when the cursor is put in the cell.

My query is selecting where Date1 is not equal to Date2, but is returning
some rows where the dates are equal.
Click to expand...


Use the DateValue function:

WHERE DateValue(Date1) = DateValue(Date2)
 
or change your query criteria to handle date.time fields:

WHERE [DATE] <> #date#
becomes
WHERE ([DATE] < #date#) or ([DATE] >= #date# + 1)

Time values are fractional days. DATE is DATE.0
DATE.1 is DATE plus one tenth of a day.

(david)
 
Thanks for responding, Ken. That was the first thing I tried, but I got the
error "Data type mismatch in criteria expression". I don't get it because
they are both dates.

I added a column NewDate: DateValue([Date1]) and then in the criteria line
for that column I entered <> DateValue([Date2])

Ideas?

Thanks again.

Ken Snell said:
Use DateValue to extract just the date value and then do the comparison.

TestDateValue: DateValue([FieldName1])
Criteria: =DateValue([Field2])
--

Ken Snell
<MS ACCESS MVP>

KADL said:
I am having trouble comparing two dates in a table because some of the
values
have a time included. I see it when the cursor is put in the cell.

My query is selecting where Date1 is not equal to Date2, but is returning
some rows where the dates are equal.

What do I do?

Thanks!
Click to expand...
Click to expand...
 
Thanks for responding, Marshall. Please view my response under Ken's response.

Your thoughts?
 
This is an interesting one, David. I had to put comparable criteria in both
date's column because some slipped through where the opposite field was the
one with the time included.

Thanks!

david epsom dot com dot au said:
or change your query criteria to handle date.time fields:

WHERE [DATE] <> #date#
becomes
WHERE ([DATE] < #date#) or ([DATE] >= #date# + 1)

Time values are fractional days. DATE is DATE.0
DATE.1 is DATE plus one tenth of a day.

(david)

KADL said:
I am having trouble comparing two dates in a table because some of the
values
have a time included. I see it when the cursor is put in the cell.

My query is selecting where Date1 is not equal to Date2, but is returning
some rows where the dates are equal.

What do I do?

Thanks!
Click to expand...
Click to expand...
 
If any of the fields can contain a Null value, then the DateValue function
will fail.

--

Ken Snell
<MS ACCESS MVP>

KADL said:
Thanks for responding, Ken. That was the first thing I tried, but I got
the
error "Data type mismatch in criteria expression". I don't get it because
they are both dates.

I added a column NewDate: DateValue([Date1]) and then in the criteria line
for that column I entered <> DateValue([Date2])

Ideas?

Thanks again.

Ken Snell said:
Use DateValue to extract just the date value and then do the comparison.

TestDateValue: DateValue([FieldName1])
Criteria: =DateValue([Field2])
--

Ken Snell
<MS ACCESS MVP>

KADL said:
I am having trouble comparing two dates in a table because some of the
values
have a time included. I see it when the cursor is put in the cell.

My query is selecting where Date1 is not equal to Date2, but is
returning
some rows where the dates are equal.

What do I do?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I don't see that happening. DateValue can deal with
anything that be converted to a date, certainly a DateTime
field or any Text value that can be converted to a DateTime
value. One thing DateValue can not deal with is Null, but
if that were the problem, you would get a different message.

Are you sure the two fields are really dates? Maybe you
spelled a field name incrrectly???
 
Back
Top