Changing Data format

[Sam]

I have a text field in a table for a data and time - the time is
importing as 9/5/2006 11:54:03:257.

I need to have the data formated as MM/DD/YYYY, using the example
above the date/time of 9/5/2006 11:54:03:257 should be 09/05/2006.

Please help with a formula to change this date with in a query.

thanks

 

[Duane Hookom]

You should try the DateValue() function:
RealDate: DateValue([text field in a table for a data and time])

 

[John Spencer]

DateValue will error with a string like "9/5/2006 11:54:03:257"

You have to remove the last 4 characters (assumption being that the
milliseconds are always expressed as three digits) or just grab the date portion.

DateValue(Left([DateString],Len([DateString])-4))

or

DateValue(Left([DateString],Instr(1,[DateString]," ")-1))

If Datestring is null or zero-length or cannot be interpreted as a date, you
will get an error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hans Up]

I have a text field in a table for a data and time - the time is
importing as 9/5/2006 11:54:03:257.

I need to have the data formated as MM/DD/YYYY, using the example
above the date/time of 9/5/2006 11:54:03:257 should be 09/05/2006.

My guess is that you intend 09/05/2006 to remain a text value, instead
of converting it to date data type. Let us know if I guessed wrong.

Please help with a formula to change this date with in a query.

Replace YDT with the name of your field which contains the date text values:

FormattedDate: Format(Left([YDT],Instr(1,[YDT]," ")-1),"mm/dd/yyyy")