DateDiff with different formats

P

Peter

Hi,

I am trying to use DateDiff where one date is in Date/Time format and the
other is a text field in the format MMDDYY. Having no luck. Any suggestions
on how to get this to work. Am using Access 2002.

Thanks for any help.
 
K

KARL DEWEY

Use this to change the text to a date --
DateSerial("20" &
Right([YourField],2),Left([YourField],2),Right(Left([YourField],4),2))
 
D

Duane Hookom

I try to explicitly convert all text values to numbers and then dates:

DateSerial(2000 + Val(Right([field],2)), Val(Left([field],2)),
Val(Mid([Field],3,2)))
 
V

Van T. Dinh

.... and another variation:

DateSerial( Right([TextField],2), Left([TextField],2),
Mid([TextField],3,2) )

which rely on automatic type-casting and the default century as set in
Windows OS.
 
J

John Spencer

What the heck. One more

DateValue(Format(DateString,"@@/@@/@@"))

If you are really paranoid about the date string being valid.

IIF(IsDate(Format(DateString,"@@/@@/@@")),
DateValue(Format(DateString,"@@/@@/@@")),Null)
 
Top