Date Import Problem

[jesusoneez]

Mile-O-Phile was kind enough to help me with a related problem regarding converting a Text field into a Date/Time field (format "YYYYMMDD").

This has led to the problem that now the field is Date/Time, the txt file that I try to import into this table won't work as "YYYYMMDD" isn't a default Date/Time format. The import fails.

So, how do I tell Access the Date/Time format is "YYYYMMDD" before the data is imported, thus allowing the import to happen?

I'm thinking some like;

Format[MyDate], "YYYYMMDD"

but I don't know physically where I'd place this function (which tends to be my problem half the time!).

Regards,

Steve

 

[stevejeffries]

new user query re DateAdd

I have been asked to create a simple database for work. One of the functions that I need to use is DateAdd. Can anyone explain how I can get 'field 2' to autofill with a date 3 months into the future from a date entered in 'field 1'.

TIA

Steve

 

[neileg]

Steve Swallow
The way I read the advice Mile gave you was that the data would be held in your table as text and converted in a query to date/time. I don't understand how this prevents you importing text into a text field. Please explain.

Steve Jeffries
Don't piggyback on someone else's query, start your own. In any event, you should not hold field2 at all since you can always calculate this by adding three months to field1 in a query or in a form.

 

[jesusoneez]

Neil,

The hope was to convert all data currently residing in my Tables to a Date/Time format as opposed to Text. The initial data from the mainframe is technically a number (it's not in quotes to denote a text field, although the OrderDate field in the table is set as an 8 digit text field). For example;

MAINFRAME DATA
============

20030101,"L'OREAL",04664,"SPEC001".....
20030624,"DISNEY",05182,"SPEC231".....

END
===

Obviously I can import the YYYYMMDD as text or a number format if I wanted, but it needs to be imported as Date/Time.

I used Mile's advice to convert my current Text fields to Date/Time without problem, but importing new data (as above) results in an error as it doesn't recognise YYYYMMDD as a date format.

I need Access to recognise that YYYYMMDD is a date so it'll import OK.

The mainframe programmer can reformat the output text files so it'd be more like;

01/01/2003,"L'OREAL",04664,"SPEC001".....

But it'd take him some time, hence me wondering if I could do it from the Access side at import time.

Hope that's cleared it up a bit!

 

[jesusoneez]

After all that, something so simple!

Thanks all for your help.

Regards,

Steve