Acces 2003 Imprt Date Field Fails

[Rickinrsm]

I have a large delimited table that I import into Acces 2003.

The date format is mm/dd/yyyy:00:00:00 or something like that.

If I set the Date field properties in the import to "text" it comes in fine with all of the characters as shown above. (I really don't need the time thing.)

If I set the Date field properties to "Date/Time" (The only choice I seem to have) none of the date values are imported.

Any ideas?

Thanks much . . .



Rick ~ Access Newbie

 

[dhookom]

Import into a temporary table with a text field. Then use an append to update your permanent table. You can use lots of functions to convert your text to a date.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rickinrsm]

Dhookum . . .

I thought that an Append query added additional records to the end of a table.

Can an append (query?) be used to format data in fields? Not adding records but just formatting fields?

Will this work with Ucase also?

I need to change eight Date fields to the Date format from a text forma,t and six fields to all UPPER CAPS. I don't like the Expression thing as it changes the name of the fields in the original table and I run calculations based on field names.

What's the easiest way to accomplish this?

Thanks . . .





Rick ~ Access Newbie

 

[dhookom]

My solution is to import the large delimited table into an temporary empty Access table so that all the information comes in. You can then append the records from the temporary table into your permanent table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredericofonseca]

if you date is EXACTLY as you mentioned then you need to use temporary tables and functions to do what you need.

If it's not, and if the format is as follows

"mm/dd/yyyy 00:00:00" (e.g. note the space between date and time values), then you can and should use the import wizard supplied by Access, and when on it use the advanced tab (lower left corner)
Once on this new window you will be able to set your date formats, your date and time separators and other info.
I would advise you to save this specification (save as button) for use afterwards if you need.

Using the above I was able to successfully import the following data into a (text, datetime) table combination

entry 1,01/23/2004:01:01:01
entry 2,02/23/2004:01:01:01
entry 3,03/23/2004:01:01:01
entry 4,04/23/2004:01:01:01


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Sorry small correction.

My data was

entry 1,01/23/2004 01:01:01
entry 2,02/23/2004 01:01:01
entry 3,03/23/2004 01:01:01
entry 4,04/23/2004 01:01:01

And upon thinking on this again, even with 02/23/2004:01:01:01 you can use the wizard and split the field, ignoring the time part, so you can use the wizard regardless of your field formating. No need to use temporary tables or functions.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Rickinrsm]

I've used the "Advanced" features AFTER the import and found them very helpfull.

Once imported I can format everything the way I need it using the datasheetDesign function.

Once this is done, all future or saved queries have all of the data formatted the way I need.

Kewl!

Thanks everyone

Rick ~ Access Newbie