You can set the format properties for a field in a Union query, but you need
to use code. You cannot do it in the Access user interface.
Here is a sample procedure that sets query field properties using code. It
uses a pretty standard procedure named ResetProperty that creates a property
if it does not exist, then sets it (many variations have been posted here
before). You can do something similar to what is below, but set the format
to "Currency" instead of "Standard".
Of course, if you are displaying the query using a form (as you normally
should), then you can simply set the form control properties directly.
- Steve
' Set the format for a query field to Standard with a specified number of
decimals.
' This is especially useful for Union queries where it is difficult to set
the properties manually.
' Example: QuerySetNumeric FieldDecimals DbEngine(0)(0), "MyUnionQue ry", 3,
"Total Quantity"
Public Function QuerySetNumeric FieldDecimals(d b As DAO.Database,
strQueryName As String, bytDecimalPlace s As Byte, strFieldName As String) As
Boolean
On Error GoTo ErrExit
Dim qdf As QueryDef, fld As DAO.Field
Set qdf = db.QueryDefs(st rQueryName)
Set fld = qdf.Fields(strF ieldName)
ResetProperty fld, "Format", "Standard", dbText
ResetProperty fld, "DecimalPlaces" , bytDecimalPlace s, dbByte
Set fld = Nothing
Set qdf = Nothing
Exit Function
ErrExit:
Set fld = Nothing
Set qdf = Nothing
MsgBox "Cannot set decimals for query " & strQueryName & cDoubleLine &
Err.Description , vbOKOnly, "Problem Setting Query Decimals"
End Function
"DD" <da**********@b igpond.com.au> wrote in message
news:14******** *************** ***@posting.goo gle.com...
I have a UnionQry that is run from StatementsI and StatementP.
the Due and Paid colunms are currency and show the $
however when i union the two the currency format has gone.
Any help please
SELECT * FROM qryStatementI
UNION SELECT * FROM qryStatementP
ORDER BY thedate;
