Converting Date & Time Code to Date & Time!

[Ally]

Hi

Not been using Access for a while and back using it again. I've got a table that I'm importing from Excel which is originally a download from another system. The date fields are mostly set as Date & Time so are displaying like: 09/04/2009 11:30:00

When I import these into Access they're displaying as: 39912.479167

I'm normally okay about translating these back whilst in Excel, when it's just the date, but am very unsure about it 1) in Access and 2) as date and time!

I have looked through the old posts but haven't managed to find anything!

I'd be grateful for any help please!! Thank you.

 

[namliam]

This is just the numeric display of date and time...

A date is the integer part: 39912
Time is the decimal: .479167 (note 0.5 days = 12 hours)

Simply change your column to date/time and you should be done

 

[neileg]

That's the way Access (and Excel) store date and time. The integer part is the date, the fractional part is the time. Just format this as date & time, no conversion needed.

 

[Ally]

This is just the numeric display of date and time...

A date is the integer part: 39912
Time is the decimal: .479167 (note 0.5 days = 12 hours)

Simply change your column to date/time and you should be done

Unfortunately, I have tried this already and it clears everything and converts it to Null!

 

[wiklendt]

try creating a new table with the appropriate data types and append from the imported table to the new table...

 

[Ally]

try creating a new table with the appropriate data types and append from the imported table to the new table...

Thank you ... but I had already tried that and it converts them all to null.

One thing I did just try was Format in a query: Format([DtTm_FirstOnsetSymp],"dd/mm/yy hh:mm:ss")

This does work although it duplicates each record by about 3! ???

 

[wiklendt]

Thank you ... but I had already tried that and it converts them all to null.

One thing I did just try was Format in a query: Format([DtTm_FirstOnsetSymp],"dd/mm/yy hh:mm:ss")

This does work although it duplicates each record by about 3! ???

did you have more than one table/query in the query design view? if so, it may be pulling up related records thereby duplicating some values (like your date/time field).

try selecting unique values only from the properties of the query OR typing in "DISTINCT" after the "SELECT" in SQL view of the query.

this may (or may not) give desired results...

after you are successful in attaining the correct format, i would suggest turning that select query into a make table query OR an append query of an existing correctly-datatyped table.

see how you go.

 

[Ally]

did you have more than one table/query in the query design view? if so, it may be pulling up related records thereby duplicating some values (like your date/time field).

try selecting unique values only from the properties of the query OR typing in "DISTINCT" after the "SELECT" in SQL view of the query.

this may (or may not) give desired results...

after you are successful in attaining the correct format, i would suggest turning that select query into a make table query OR an append query of an existing correctly-datatyped table.

see how you go.

It may be easier if I tell you exactly how I'm doing this (sorry I probably should have done this first!)

Once the table is downloaded into Excel from the external db, I'm importing (at the moment just copying and pasting, but will try and do some sort of automated function).

Then I'm appending (via Append query) to a clean table where I've set up all the field names to what I want as the ones in the Excel table are long and convoluted! Again I will need to clear the data from the original "clean" table each time via some macro or code, but doing a make table is not an option as I need to keep the field names I've set up.

I have then just done a new query to try out the Format using the data I've just appended. Nothing else is open.

I used to be quite good at Access but haven't used it for so long I'm very rusty!

 

[DCrake]

Can you post a sample of the spreadsheet to look at the layout and formatting of the columns.

David

 

[Ally]

View attachment StrokeData_Sample.xls
Here you go - thank you!

 

[DCrake]

When importing data from external sources you have to remember certain things:

1. If you want the import to a new file or an existing file then row 1 must contain the field names, unless you specifiy otherwise.

2. The column headings in row 1, if used as field names, must not contain invalid characters such as / , ? ; :

3. Column heads should not be more than 64 characters long.


I edited your spreadsheet. Removed rows 1,2 & 3. Changed the column headings text to remove illegal characters then imported it. No erros were detected.

David

 

[Ally]

When importing data from external sources you have to remember certain things:

1. If you want the import to a new file or an existing file then row 1 must contain the field names, unless you specifiy otherwise.

2. The column headings in row 1, if used as field names, must not contain invalid characters such as / , ? ; :

3. Column heads should not be more than 64 characters long.


I edited your spreadsheet. Removed rows 1,2 & 3. Changed the column headings text to remove illegal characters then imported it. No erros were detected.

David

The reason the column headings are all over the place is because they're from an external source that I have no control over and I realise the problems with this. So I was copying and pasting just the data I needed, then appending that to a table with my pre-defined field names.

But this works! If that's all it's going to take is deleting a few rows etc, then that's great! Thank you!