format date question?

[Russ via AccessMonster.com]

I have a csv file with a date field, how could I format this; 20061002120000
[0:GMT] into a real date like 10/21/2006 so access will understand it?

 

[Guest]

how could I format this; 20061002120000[0:GMT] into a real date like
10/21/2006 so access will understand it?
You need to 'parse' it, not format. One question - is there a typo of an
extra zero between the nonth and day?

Assuming it is a typo then use this ---

DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right(Left([YourFieldName],8),2))

If it is not a typo then use this ---

DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right(Left([YourFieldName],9),2))

 

[Russ via AccessMonster.com]

thanks for the help, it is not a typo, and I must be doing something wrong
because not providing correct date.

Fyi. 20061002120000[0:GMT] is Year / Month/ Day format have no idea what the
other numbers represent.

Not sure how to use the DateSerial but this is what I used;
DateSerial(Left([Date],4),Right(Left([Date],6),2),Right(Left([Date],9),2))

Russ

how could I format this; 20061002120000[0:GMT] into a real date like

10/21/2006 so access will understand it?
You need to 'parse' it, not format. One question - is there a typo of an
extra zero between the nonth and day?

Assuming it is a typo then use this ---

DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right(Left([YourFieldName],8),2))

If it is not a typo then use this ---

DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right(Left([YourFieldName],9),2))

I have a csv file with a date field, how could I format this; 20061002120000
[0:GMT] into a real date like 10/21/2006 so access will understand it?

 

[Russ via AccessMonster.com]

Ok, my error. I provided wrong information in first post 20061002120000[0:GMT]
is not 10/21/2006 it is 10/02/2006
I used;
DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right
(Left([YourFieldName],8),2))
and works!
Thanks!!!

thanks for the help, it is not a typo, and I must be doing something wrong
because not providing correct date.

Fyi. 20061002120000[0:GMT] is Year / Month/ Day format have no idea what the
other numbers represent.

Not sure how to use the DateSerial but this is what I used;
DateSerial(Left([Date],4),Right(Left([Date],6),2),Right(Left([Date],9),2))

Russ

how could I format this; 20061002120000[0:GMT] into a real date like

10/21/2006 so access will understand it?

[quoted text clipped - 11 lines]

I have a csv file with a date field, how could I format this; 20061002120000
[0:GMT] into a real date like 10/21/2006 so access will understand it?

 

[Marshall Barton]

I used;
DateSerial(Left([YourFieldName],4),Right(Left([YourFieldName],6),2),Right
(Left([YourFieldName],8),2))
and works!

Use the Mid function stead of Right(Left(

DateSerial(Left(Field,4), Mid([Field,5,2), Mid(Field,7,2))

The other digits are probably the time value as hours
minutes seconds.