Date format Query (1 Viewer)

D

deadlywonky

New member

Local time

Today, 04:02

Joined

Sep 24, 2012

Messages

5

• Sep 24, 2012

• #1

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

Super Moderator

Local time

Today, 04:02

Joined

Nov 30, 2011

Messages

8,494

• Sep 24, 2012

• #2

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??

 

D

deadlywonky

New member

Local time

Today, 04:02

Joined

Sep 24, 2012

Messages

5

• Sep 24, 2012

• #3

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

Super Moderator

Local time

Today, 04:02

Joined

Nov 30, 2011

Messages

8,494

• Sep 24, 2012

• #4

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?

 

D

deadlywonky

New member

Local time

Today, 04:02

Joined

Sep 24, 2012

Messages

5

• Sep 25, 2012

• #5

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

Super Moderator

Local time

Today, 04:02

Joined

Nov 30, 2011

Messages

8,494

• Sep 25, 2012

• #6

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

 

S

Severin

Snr. Developer

Local time

Yesterday, 20:02

Joined

Mar 27, 2012

Messages

172

• Sep 25, 2012

• #7

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

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

 

D

deadlywonky

New member

Local time

Today, 04:02

Joined

Sep 24, 2012

Messages

5

• Sep 25, 2012

• #8

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

Smeghead

Local time

Yesterday, 20:02

Joined

Jan 12, 2001

Messages

32,059

• Sep 25, 2012

• #9

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.

 

D

deadlywonky

New member

Local time

Today, 04:02

Joined

Sep 24, 2012

Messages

5

• Oct 1, 2012

• #10

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

 

boblarson

Smeghead

Local time

Yesterday, 20:02

Joined

Jan 12, 2001

Messages

32,059

• Oct 1, 2012

• #11

deadlywonky said:

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

Click to expand...

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.