Delimited Date/Time Fields Being Deleted on Import?

[crazy_loud]

For anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...

I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:

4/2/2007 8:30:00 AM​

Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?

Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...

All those better than I feel free to show me how and where I've been stupid

 

[crazy_loud]

For the record...

I've just been told on another forum to gut the time section to make the import work, but I've decided not to... I'll try something else and advise.

 

[RuralGuy]

I think I would import it as a text field and convert it to a DateTime value in another process.

 

[crazy_loud]

In case you wanted to know...

For those of you who want to know, I changed the format of the applicable date/time field that was having its values dropped upon import to...

mm/dd/yyyy hh:nn:ss AM/PM​

and no longer have any lost date/time field values upon import to my table.

Why I need to clearly tell Access to use this particular format on a "Date/Time" field as opposed to just leaving the format blank by default, I will never know - but I'm biased...

 

[The_Doc_Man]

I've had that problem too. I usually just leave it as a text field and use queries with the CDate function when I really need the time field as a time.

But lately I've discovered that there is another approach that sometimes works when all I wanted to do was chart something. Convert the date to a date - but then convert it a second time to a DOUBLE.

When you chart observation vs. DOUBLE (time) you can do GREAT scatter charts and then tell Access or Excel to convert the axis format to TIME (picking mm/dd/yyyy hh:nn format). Ac97 used to be a royal pain in the patootie about trying to plot scatter charts of "observation vs time" and I thought it couldn't get worse. THEN my employer's site got us all copies of Office 2003 - and it got worse. I now export queries back to Excel to do massive data analysis because Access chart interface is worse than ever - IMHO.

 

[Dennisk]

Access uses a default import specification. If you create your own then you can fine tune it to any desired date/time field and save it. This specification can then be used in a TransferText operation.

 

[crazy_loud]

UPDATE - I must have been on something yesterday, because my resolution does not work today.

Sadly, you cannot create an import specification for date/time fields that also dictates the treatment of the "AM/PM" portion of a field value as being part of a date/time format, from what I can see, so I'm still in trouble as Access is still acting like that AM/PM part of these fields has some horribly contagious disease that must be exterminated via import...

Access 2000 still losing date/time field values on import, and I'm still losing hair

 

[Weeze155]

And here I am, a poor noob on Access having the same problem and looking for fixes to this bummer.
However, I was particularly struck by this bug, thanks to a condition in particular: I was importing a file with 3 columns, all with the same data and format (automatically created and downloaded, so no human error). Access seemed reluctan to import the last of three columns, which unlike the others had some empty values. The first two of course has always been imported perfectly fine in my several attempts.
Then the discovery: on Excel, the null values were not formatted as the other ones.
I applied the same date formatting to the whole column and then it all went smooth.

Please, you try it and then tell me if it fixes for you!

Cheers

 

[CJ_London]

After 16 years I suspect the OP has a solution

 

[theDBguy]

Hi @Weeze155

Welcome to AWF!