Format Problem!!

[bidmet]

In a Query I have used an expression as follows (Time field being format of dd/mm/yyyy hh:mm:ss).

Month: Format([Time], "mmmm")

This gives an output of for example 'April' in the Month field.

This data is then called into Excel and shows 'April'.

I want to show 'April 04' for example.

I changed the expression to Month: Format([Time], "mmmm yy").

The query shows 'April 04', but Excel shows 01/04/2004 0:00:00.

How can I rectify this problem?

N.B. Unable to change the format of the Time field, from the orginal Database.

 

[bat17]

How are you exporting to Excel? I tried copying and pasting from the Query and "Analyze with Excel" and in both cases it exported as text.

You could change the format in xl to show MMM YY

Peter

 

[bidmet]

Thanks Peter, but changing the format in excel only changes the way it look on the sheet. The cell is still wrong as in the top box it still shows dd/mm/yyyy hh:mm:ss.

Using excel I am importing the data from access via the import data function.

In every case other than this one the formating and data is the same as in the database.

 

[bat17]

It appears then that the problem is at the excel end then, XL is rcognising it as a date and converting it from text to number and formating as date. My copy of MsQuery seems to be broken at the moment so I cannot check it out but you may be able to apply formatting there that will stick with the spreadsheet. I seem to recal that there are options about formating so that you can set the formats in the sheet and query will use those rather than guessing.
It may well be worth raising the topic on the XL board to find out.

Peter

 

[Abulafia]

As an aside: Time is a reserved word in Access and should not be used as a field name.