Date/Time formatting

[Guest]

I need to extract data from several dynamic linked tables. Date/time data is
provided in varying formats such as 9/9/2004 - 5:12:05 AM, 12/30/2004 -
9:49:13 AM, or 11/8/04 5:33:05 AM. To sort in Access I need all to appear
mm/dd/yyyy only or else I get the #Num! error message. How do I accomplish
this without manual editing these docs? I'm sure it is easy for you but new
to me. Thanks in advance.

 

[Ken Snell [MVP]]

Do those tables store text strings of the date/time combinations? or are
those fields set to a date/time data type and these are just variations in
how the data are displayed?

Assuming that they are text strings (which would be a weird way to store
date/time data), you can extract just the date info using a few, built-in
functions:

TheDateOnly: CDate(Mid([DateFieldName], InStr([DateFieldName], " ") - 1))