Maintaining Cell Format in Query

[Mike]

I have this problem that the cell format in the query result changed from the
source document. For example, when I run a query on a date field, the display
result will be shown as numbers. I have to keep reformatting the cell
everytime I run new query, and refreshing the data.

How do I retain the format in a query to be exactly as the source document?
To add headache to my problem, I have both numbers and date on the same
field, therefore I cannot format the whole column as date.

I'm using Excel 2003, and source document also in Excel 2003 format.

Thanks in advance for your expert tips.

 

[Roger Govier]

Hi Mike

Post the query that you are using, and what is in the cells involved in the
query.

 

[Mike]

Hi Roger,

The following is the query:

Next Due Remaining F16 200H/3M
39745.25 63.25 D M
223.8 -1776.2 H M
39753.5 71.5 D M
218.2 -1781.8 H M
500 -1500 H M
1500 -500 H M
1108.2 -891.8 H M
249.7 -1750.3 H M
39745.25 63.25 D M
M
200 -1800 H M
172.6 -1827.4 H M
223.8 -1776.2 H M
500 -1500 H M
500 -1500 H M
39722.25 40.25 D M
1200 -800 H M
600 -1400 H M
39684.41667 2.416666667 D M
39753.5 71.5 D M
39753.5 71.5 D M
223.8 -1776.2 H M
31000 100 U M
31000 100 U M
559.6 -1440.4 H M
223.8 -1776.2 H M
500 -1500 H M
39721 39 D M
1100 -900 H M
500 -1500 H M
39691 9 D M
223.8 71.8 H M
39753.5 71.5 D M
228.5 76.5 H M


As you can see from the query above, the query return with inconsistent cell
format. For example, then Field F16=D, the "next due" cell is suppose to be a
date format in the source document, and "remaining" should be absolute number
represent number of days, which I do not need the decimal point. The source
document is formatted correctly, and I had selected "preserve cell format"
box in the query option. When F16=H, then the "next due" format suppose to
have 1 decimal point, in which, H represent Hours, and when F16=U, the "next
due" format must have no decimal point. Hope my explaination make sense to
you.

Regards,
Michael