DateDiff with different formats

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

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.
 
Use this to change the text to a date --
DateSerial("20" &
Right([YourField],2),Left([YourField],2),Right(Left([YourField],4),2))
 
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)))
 
.... 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.
 
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)
 
Back
Top