How to use format function in a query ?

[Hapr1]

HOw to use the format function . I had tried to use it but does not work for me .
when i click properties of the particular column in a query and go to format tab , i type in mm:dd:yy( i want to change the format of date ) and then execute the query but nothing happens.
can someone help me ?

 

[ejstefl]

Try picking "Short Date" from the drop down list.

 

[Hapr1]

When used short date it works in correct format in access but when
the macro runs and exports the file to excel it ges back to the same format like 17-jun-05.
Any ideas?

 

[Pat Hartman]

Select ...., Format(YourDate, "mm:dd:yy") As FormattedDate
From YourTable;

Use the format function which will actually turn the date into a text string so Excell won't reformat it. The Format property only affects how the date is displayed when you open the query an look at the recordset as a datasheet.

 

[ejstefl]

Pat,

I always have the same problem when exporting to Excel. Is there a way to keep the field in a Date format when you export to Excel, and have the format of the date kept as whatever it is in Access?

As an example, I have a date field in Access formated to short date. It has a value of 1/1/05. When I export to Excel, is there a way for Excel to display "1/1/05" while keeping it as a date type, instead of a text?

 

[Pat Hartman]

I always have the same problem when exporting to Excel. Is there a way to keep the field in a Date format when you export to Excel, and have the format of the date kept as whatever it is in Access?

No. Excel has its own date format default which I think is whatever is defined as the windows short date format. You would have to export the unformatted date from Access and then modify the format in the spreadsheet. You can do this from within Access by automating Excel. To learn about automation, you can search the MSDN library. They have some good articles. You can also search here.

A Tip: When trying to automate features of Word, Excel, or anyother product that supports macros, I create a macro that does what I want to do and copy the VBA code that it generated to give me a start. While macro code is not usually pretty, it will give you a better idea of what you need to do faster than reading any manual will.