DateDiff with different formats

[Guest]

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.

 

[Guest]

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

 

[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)))

 

[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.

 

[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)