How do I set query field formats at vba runtime

Question

I am creating a Crosstab query at runtime with the option of using a summary type of "SUM" or "COUNT". If I set Sum, then I want an output format of "$#,##0.00", and if it is Count, then I want an output format of "#,##0". There are lots of discussion questions setting a tableDef Field property to format output, but there is nothing that I can find for setting the output format for a query Field.

Answer 1

You can use format with dynamic SQL:

SELECT Format(SomeField, "$#,##0.00") ...

Answer 2

If it's a saved QueryDef, the format can be set in the columns properties sheet in the query designer. In the QBE grid, right click on the column and display the properties sheet. The Format property should be the second line of the property sheet.