Formatting fields....

[Rob Wills]

Normally, if I want to format a field in a query – I set the properties in
the field using the GUI...

However, I’m doing a funky join, which the GUI is unable to represent...

So I’d have thought that using format() in the SQL would be the best way –
e.g. format([fieldname],â€#,##0.00â€)

But this appears to change the data type to text and left align it, which
makes it look .....Special.....

Any ideas?

 

[Marshall Barton]

Normally, if I want to format a field in a query – I set the properties in
the field using the GUI...

However, I’m doing a funky join, which the GUI is unable to represent...

So I’d have thought that using format() in the SQL would be the best way –
e.g. format([fieldname],”#,##0.00”)

But this appears to change the data type to text and left align it, which
makes it look .....Special.....

That's another reason why you should not use a table or
query's datasheet view to look at data. Instead you should
leave the formatting to forms and reports where you can
specify the Format property of a text box without messing
with the data type.

 

[Allen Browne]

You may be able to choose just this one field or expression from limited
tables with a simple join, set the Format property in design view, and then
switch to SQL View and hack the changes.

Alternatively, you could write some VBA code to CreateProperty on the Field
in the QueryDef. There's an example of creating fields and their properties
in a table (TableDef rather than QueryDef) in this link:
http://allenbrowne.com/func-DAO.html#CreateTableDAO

In the end, the property reference would be:
CurrentDb.QueryDefs("Query1").Fields("Expr1").Properties("Format")