Problems with Date conversion from text format into date/time...

[Annie D via AccessMonster.com]

Problem!

I have a series of CSV files that I am bringing in to Access, contained
within each of them are 6 date fields, however, they are not being recognized
as dates…this is a big problem! the date looks like this:

20050816 23:59:59.000

I wrote a query to split up the numbers and then bring them back together in
the right order:

L1: Left([DATA_START_DATE],4)
M1: (Mid([DATA_START_DATE],5,2))
M1b: (Mid([DATA_START_DATE],7,2))
NEW_DATA_START_DATE: ([M1b] & "/" & [M1] & "/" & [L1])
DATA_START_DATE _2: IIf([NEW_DATA_START_DATE]="//","",[NEW_DATA_START_DATE])

Now it looks like this:

16/08/2005

This is fine, however; this is still text and not a date…. I firstly tried to
do a make table query, then go in to design view to change the field type
from text to date/time, this won’t work. I then made a blank table and
changed the field types in design view, and made my original query an append
query to put the data in to it. Which it did, but a message is appearing that
access has set 48067 fields to null and I can’t understand why?

If anyone can think or a better way of trying to do this I would be most
grateful, or even point out where I might be going wrong will do!

Thank you
--
Message posted via http://www.accessmonster.com

[Terry Kreft]

There's a few ways to tackle this. below is one

Where the data is held in Table2 in a field called df

SELECT
df,
Left([df],4) AS df_year,
Mid([df],5,2) AS df_month,
Mid([df],7,2) AS df_day,
DateSerial([df_year],[df_month],[df_day]) AS df_date
FROM Table2

The df_date field is returning a date (not the string representaion of a
date that you are getting now).

--
Terry Kreft
MVP Microsoft Access
"Annie D via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:54***********@AccessMonster.com...

Problem!

I have a series of CSV files that I am bringing in to Access, contained
within each of them are 6 date fields, however, they are not being recognized as dates.this is a big problem! the date looks like this:

20050816 23:59:59.000

I wrote a query to split up the numbers and then bring them back together in the right order:

L1: Left([DATA_START_DATE],4)
M1: (Mid([DATA_START_DATE],5,2))
M1b: (Mid([DATA_START_DATE],7,2))
NEW_DATA_START_DATE: ([M1b] & "/" & [M1] & "/" & [L1])
DATA_START_DATE _2: IIf([NEW_DATA_START_DATE]="//","",[NEW_DATA_START_DATE])
Now it looks like this:

16/08/2005

This is fine, however; this is still text and not a date.. I firstly tried to do a make table query, then go in to design view to change the field type
from text to date/time, this won't work. I then made a blank table and
changed the field types in design view, and made my original query an append query to put the data in to it. Which it did, but a message is appearing that access has set 48067 fields to null and I can't understand why?

If anyone can think or a better way of trying to do this I would be most
grateful, or even point out where I might be going wrong will do!

Thank you
--
Message posted via http://www.accessmonster.com

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's one way (all one line):

cdate(format(left([DATA_START_DATE],8),"####-##-##")) +
cdate(format(mid([DATA_START_DATE],10,8)))

This will not include the milliseconds.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyibU4echKqOuFEgEQJ+oACgyK5HMN2qsEwnM7LQnSw+1X 3NQDcAoKCw
9drI52cA4twc9iGkX5pDmgaG
=WdLn
-----END PGP SIGNATURE-----

Annie D via AccessMonster.com wrote:

Problem!

I have a series of CSV files that I am bringing in to Access, contained
within each of them are 6 date fields, however, they are not being recognized
as dates…this is a big problem! the date looks like this:

20050816 23:59:59.000

I wrote a query to split up the numbers and then bring them back together in
the right order:

L1: Left([DATA_START_DATE],4)
M1: (Mid([DATA_START_DATE],5,2))
M1b: (Mid([DATA_START_DATE],7,2))
NEW_DATA_START_DATE: ([M1b] & "/" & [M1] & "/" & [L1])
DATA_START_DATE _2: IIf([NEW_DATA_START_DATE]="//","",[NEW_DATA_START_DATE])

Now it looks like this:

16/08/2005

This is fine, however; this is still text and not a date…. I firstly tried to
do a make table query, then go in to design view to change the field type
from text to date/time, this won’t work. I then made a blank table and
changed the field types in design view, and made my original query an append
query to put the data in to it. Which it did, but a message is appearing that
access has set 48067 fields to null and I can’t understand why?

If anyone can think or a better way of trying to do this I would be most
grateful, or even point out where I might be going wrong will do!

Thank you

[Annie D via AccessMonster.com]

Hi,

This would be fantastic if only i could get it work!....its creating the
fields but not putting any data in to them!

Thank you so much, i will persevere with it.

Annie
Terry Kreft wrote:

There's a few ways to tackle this. below is one

Where the data is held in Table2 in a field called df

SELECT
df,
Left([df],4) AS df_year,
Mid([df],5,2) AS df_month,
Mid([df],7,2) AS df_day,
DateSerial([df_year],[df_month],[df_day]) AS df_date
FROM Table2

The df_date field is returning a date (not the string representaion of a
date that you are getting now).

Problem!

[quoted text clipped - 18 lines]

This is fine, however; this is still text and not a date.. I firstly tried

to

do a make table query, then go in to design view to change the field type
from text to date/time, this won't work. I then made a blank table and

[quoted text clipped - 6 lines]

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1

[Annie D via AccessMonster.com]

Hi,

This would be fantastic if only i could get it work!

Thank you so much, i will persevere with it.
MGFoster wrote:

Here's one way (all one line):

cdate(format(left([DATA_START_DATE],8),"####-##-##")) +
cdate(format(mid([DATA_START_DATE],10,8)))

This will not include the milliseconds.

Problem!

[quoted text clipped - 28 lines]

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200509/1