I need to format the date fields in a table from MM/DD/YYYY to DD/MM/YYYY- how to do this easily 1

[Pack10]

I need to format the date fields in a table from MM/DD/YYYY to DD/MM/YYYY ...
Whats the best way to do this?

 

[PHV]

The best way is to store the values as DateTime type and then you can display them in whatever format you want.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

Sorry PHV, I wasn't clear. I need to extract the records in the table and convert them to DD/MM/YYYY.
is there a way to do this without using Mid$ and extracting the parts ....

 

[SkipVought]

A DATE is a DATE! Dates are just NUMBERS, like today is 41401. You can FORMAT your date in any number of ways. Where is it that you need this particular format: in a report?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What is the data type of your "d&ate" field ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

It's a date field

 

[PHV]

So, where is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

I need to convert it to dd/mm/yyyy
For export to excel

 

[PHV]

Why not simply use the Format function ?
SELECT Format([yourDateField],'dd/mm/yyyy') ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

...and keep in mind that the Format() function, returns a STRING, that makes date calculations difficult. Format() works just fine for viewing in a report.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

I need to convert it to dd/mm/yyyy
For export to excel

Why not just format cells in Excel?


Randy

 

[Pack10]

Hi - Its working ok...but the Column header has the quotes after the "AS". How can I show the column without the quotes, otherwise it is what I needed.

here's what I have
Format([Combined Quality Tracker].[1st Fail Date], 'dd/mm/yyyy') As "1st Fail Date" the column head in the query displays with the quotes.

 

[SkipVought]

I need to convert it to dd/mm/yyyy
For export to excel

PLEASE do not do the format as you are doing, changing the data to TEXT!!!!

When you IMPORT into Excel, Excel will try ot help you by interpreting the strings as mm/dd/yyyy and it will be a MESS!

As I stated earlier, a DATE is a DATE.

FORMAT your dates in Excel rather than the format() function to text. It will save you lots of heartache!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Pack10]

How would I format it in excel?

 

[SkipVought]

It is a simple NUMBER FORMAT on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What happens if you replace this:
As "1st Fail Date"
with this ?
As [1st Fail Date]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

I have it in Excel. But I think this file needs to be automated as its going to India for some prcesssing.
Thats why I may still need to do it on the Access side. So, if I do, How can I stop that column heading from getting the quotes?

 

[Pack10]

that was it the [] brackets. Excellent thank you so very much