UnionQry Currency format

[DD]

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;

[Tom van Stiphout]

Re: UnionQry Currency format

On 20 Apr 2004 17:44:37 -0700, david.deacon@bi gpond.com.au (DD) wrote:

That can't be helped.
But fortunately that doesn't matter, because you as the developer is
the only one ever to see it that way. When you use the query for your
form or report, you have every opportunity to re-format the data.

-Tom.

[color=blue]
>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;[/color]

[Stephen K. Young]

Re: UnionQry Currency format

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" <david.deacon@b igpond.com.au> wrote in message
news:1412655e.0 404201644.1d88b f57@posting.goo gle.com...[color=blue]
> 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;[/color]