Maintaining Cell Format in Query

  • Thread starter Thread starter Mike
  • Start date Start date
M

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.
 
Hi Mike

Post the query that you are using, and what is in the cells involved in the
query.
 
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
 
Back
Top