Thread: Format field in query

I have a query and I need to change the format of one of the fields. All of the query data comes from the table as text. One field, qty, I need to be formatted as general so that I can take the query results and insert them into a pivot table. I tried changing the query field format to Number but that causes all zero qtys to be displayed as blank cells. As a result, my pivot table would not correctly sum this field, (I am assuming this is why. My pivot result of sum would return all zeros). The query field is titled Qty Sold.

This is how I formatted it to number:
Qty: Format([Qty Sold],"#")

When I try to format it to General by typing: Qty: Format([Qty Sold],"General") it automatically inserts 2 additional sets of quotation marks before and after the word General to give me this: Qty: Format([Qty Sold],"""General""") . As a result the Qty field in the query is populated with the word General for every record where the qty should be.

Alternatively I guess I could format is as a number but have all zeros displayed as 0 instead of blank, but I don;t know how to do this.

Help?!