date format

  • Thread starter Thread starter LG
  • Start date Start date
L

LG

If I am bringing in a txt file that is , and " delimited, how do I change the
format of the dates? Example the dates come in as 20080229, normally in
excel i just change the text to date to YMD. I tried to bring that field in
as Date/Time but it leaves the field out and puts in an error table.
What I have done previously to get around this is bring it in the access
export it out to excel and back into access. I would like to eliminate all
these steps since there are over 452,000 records.
Thanks,
Lisa
 
If I am bringing in a txt file that is , and " delimited, how do I change the
format of the dates? Example the dates come in as 20080229, normally in
excel i just change the text to date to YMD. I tried to bring that field in
as Date/Time but it leaves the field out and puts in an error table.
What I have done previously to get around this is bring it in the access
export it out to excel and back into access. I would like to eliminate all
these steps since there are over 452,000 records.
Thanks,
Lisa
Click to expand...

Access won't directly recognize 20080229 as a Date/Time value - it will
probably import to a Long Integer number if you don't specify otherwise.

You can convert it to a date with an expression like

CDate(Format([importedfield], "@@@@/@@/@@"))

or

DateSerial([importeddate] \ 10000, [importeddate] \ 100 MOD 100,
[importeddate] MOD 100))
 
I imagine there are other ways but I would bring in as text and then using a
DateTime field run an update query with this --
DateSerial(Left([TextDate],4), Mid([TextDate],5,2), Right([TextDate],2))
 
I prefer to use an expression like the following to handle nulls, empty
strings, and values that are incorrect and cannot be interpreted as dates.

IIF(IsDate(Format([TheField],"@@@@-@@-@@")),CDate(Format([TheField],"@@@@-@@-@@")),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I prefer to use an expression like the following to handle nulls, empty
strings, and values that are incorrect and cannot be interpreted as dates.

IIF(IsDate(Format([TheField],"@@@@-@@-@@")),CDate(Format([TheField],"@@@@-@@-@@")),Null)
Click to expand...

Thanks, John - that looks like it would cover all the bases. Stolen! <g>
 
Not stolen. On permanent loan.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I prefer to use an expression like the following to handle nulls, empty
strings, and values that are incorrect and cannot be interpreted as dates.

IIF(IsDate(Format([TheField],"@@@@-@@-@@")),CDate(Format([TheField],"@@@@-@@-@@")),Null)
Click to expand...

Thanks, John - that looks like it would cover all the bases. Stolen! <g>
Click to expand...
 
Thanks so much this was a life saver!!!

John Spencer said:
I prefer to use an expression like the following to handle nulls, empty
strings, and values that are incorrect and cannot be interpreted as dates.

IIF(IsDate(Format([TheField],"@@@@-@@-@@")),CDate(Format([TheField],"@@@@-@@-@@")),Null)

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

KARL said:
I imagine there are other ways but I would bring in as text and then using a
DateTime field run an update query with this --
DateSerial(Left([TextDate],4), Mid([TextDate],5,2), Right([TextDate],2))
Click to expand...
Click to expand...
 
Back
Top