468,720 Members | 1,591 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,720 developers. It's quick & easy.

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;
Nov 12 '05 #1
2 1842
On 20 Apr 2004 17:44:37 -0700, da**********@bigpond.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.

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;


Nov 12 '05 #2
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: QuerySetNumericFieldDecimals DbEngine(0)(0), "MyUnionQuery", 3,
"Total Quantity"

Public Function QuerySetNumericFieldDecimals(db As DAO.Database,
strQueryName As String, bytDecimalPlaces As Byte, strFieldName As String) As
Boolean

On Error GoTo ErrExit
Dim qdf As QueryDef, fld As DAO.Field
Set qdf = db.QueryDefs(strQueryName)
Set fld = qdf.Fields(strFieldName)
ResetProperty fld, "Format", "Standard", dbText
ResetProperty fld, "DecimalPlaces", bytDecimalPlaces, 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**********@bigpond.com.au> wrote in message
news:14**************************@posting.google.c om...
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;

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bill Stanard | last post: by
3 posts views Thread by John L | last post: by
2 posts views Thread by Dalan | last post: by
3 posts views Thread by news.shaw.net | last post: by
2 posts views Thread by DD | last post: by
2 posts views Thread by Bob Dydd | last post: by
4 posts views Thread by Gerry Abbott | last post: by
2 posts views Thread by Ian | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.