Cdate funtion inconsistency

[Mimo]

Hi,

I have 2 fields from two tables that store dates as text formats.
I am trying to compare them using an iif statement along with the CDate
function.

Table 1 stores the date as mm/dd/yyyy hh:mm:ss and Table 2 stores the date
as yyyy-mm-dd h:mm:ss.

An example:
T1 .Date 1 T2 .Date.1
Equal
4/24/2002 0:00:00 2002-04-24 00:00:00 True
4/7/2001 0:00:00 2001-04-07 00:00:00 False
5/16/2005 0:00:00 2005-05-16 00:00:00 True

I have been using iif(Not(IsNull(T1.Date1)) And Not(IsNull(T2.Date2)),
iif(CDate(T1.Date1)<> CDate(T2.Date2)),"False","True"),"T")

So far it has been going well, but I noticed that though I could tell the
dates matched, False was being returned. The False in the example should be
true. I broke it down to see what was being returned by CDate(). T1.Date1
were all being evaluated correctly (dd/mm/yyyy), however CDate would evaluate
T2.Date2 as dd/mm/yyyy for records, it is evaluating to mm/dd/yyyy. This only
happens for the dates where both the day and month are less than 13.

What can I do to make sure that all equal dates are evaluated to true?

Thanx

 

[Allen Browne]

CDate() will attempt to interpret a text representation of your date
according to your regional settings. Unfortunately, if it can't make sense
of it (e.g. if the month is 13), it will just spin the values around without
telling you. That's why it's inconsistent for months above 13.

Storing the dates as text like that is not a good idea. Even worse than the
problem above, if you open the database on another computer that has
different regional settings, the values you stored will be interpreted
differently.

Text dates do make sense in a temporary table where you are importing data
in text format, so you can manipulate them and store them as real dates. But
it makes no sense to store the fields that way in your real tables.

If you have been struggling with how to get Access to recognise your dates,
this might help:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Ken Snell \(MVP\)]

Try this:

IIf(Format(T1.Date1, "mm\/dd\/yyyy") = Format(T2.Date1, "mm\/dd\/yyyy"),
"True", "False")

 

[Mimo]

Hi Ken,

I did try however, it gave me the same results as I was getting before.
Both tables were imported as text so I have tried to break it down further
and use the Year() Month() and Day() functions but I am having no luck.

Thank you for your idea.

 

[Ken Snell \(MVP\)]

Hmm, ok.... let's try this:

IIf(Format(T1.Date1, "mm\/dd\/yyyy") =
Format(DateSerial(CInt(Left(T2.Date1,4)), CInt(Mid(T2.Date1,6,2)),
CInt(Mid(T2.Date1,9,2))), "mm\/dd\/yyyy"), "True", "False")
--

Ken Snell
<MS ACCESS MVP>