By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

UnionQry Currency format

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.