Date format Query

[deadlywonky]

Hi,

I'm importing data from Excel where the date format is : [space]01Aug 12

I'm really struggling to transfer this into a standard date format (ideally yyyy.mm.dd). all the SQL functions I've tried don't work, I'd like to avoid having to reformat it in Excel (I need to keep the file size down). Any help would be very gratefully received as my boss is breathing down the back of my neck about this, and I'm :banghead:.

Many Thanks

Tom

 

[pr2-eugin]

Hello Tom, Welcome to AWF..

A little bit more information will help.. How are you importing the file to Access, is it via VBA or are you using the Wizard? What is the format(property) set for the entire field in Excel is it General or Text or Date? What are the methods you have tried??

 

[deadlywonky]

Hi Paul,

i'm gathering data from about 50 sheets using the external data/ excel wizard. grouping into 4 queries because of 4 different formats, then grouping into 1 main query

i'm not currently specifying a format for the column, so i guess it's coming in as general?

i've tried using FORMAT in every way i can and it just returns an error

Access 2010 for dummies has no help

there are some other things but i can't remember them at the moment.

 

[pr2-eugin]

When importing, try to set the format as Date/Time.. I think that should normally set the format right.. Does the Excel sheet import at all?

 

[deadlywonky]

Hi Paul,

I've just tried this and it's still coming out in the same format, using FORMAT (datestamp, yyyy.mm.dd) just brings up the enter date box as it did before

 

[pr2-eugin]

So it is importing? into the table? with the wrong format?

 

[Severin]

If datestamp is a field in your import table then this should work:

Format([DateStamp],"yyyy\.mm\.dd")

 

[deadlywonky]

Paul,

all of the data imports, i can't reformat it into the date format i want.

Severin

thanks for the suggestion, however it hasn't worked, the format remains the same

Tom

 

[boblarson]

You will need to import this into a transitional table, where the date field is text and then you can use an append query to move it to the correct table, including creating the date the way you want.

 

[deadlywonky]

how do I make a transitional table, the help files are giving me squat.

 

[boblarson]

how do I make a transitional table, the help files are giving me squat.

To set it up, manually do an import and select NEW TABLE when you get there instead of existing table. Then for future imports you import to that table and then use one, or more, append queries to move the data to the correct location in the correct format you need.