473,225 Members | 1,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

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 2164
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bill Stanard | last post by:
I have had no success using the format function as follows (the two lines of code run one after the other): 'displays a running total of lblAmtdue.Caption 'contents in txtTotal.Text...
3
by: John L | last post by:
Well, another basic type of question...amazing how much I've forgotten - never was a VB guru, but. I can see I'll have to pick up a couple VB5 instruction books - any suggestions on a couple good...
2
by: Dalan | last post by:
This should not be an issue, but it is. I'm sure that someone knows what little piece of code is needed too persuade Access 97 to include a currency format for labels (Avery, mailing type). Have...
3
by: news.shaw.net | last post by:
I have a subform that contains a currency field. If I tab my way to the field, everything works as desired, a number can be entered, and when I leave the field it is rendered as a currency...
2
by: DD | last post by:
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...
2
by: Bob Dydd | last post by:
Hi Everbody Question Changing the regional settings ie. from UK to US AUS etc, Access retains the original currency format that the application was created in. So if an mdb that is written...
4
by: Gerry Abbott | last post by:
Hi All. How can i put a zero decimal for my currency into my format statement, == & ", for " & & " days. Cost " & Format(,"Currency") which is the data source for a textbox on a report
1
by: Nigel C | last post by:
Hi, I am going round in circles... I am trying to produce a report based upon the information gathered into a temp table. Each column (stored as a text field) in the table holds weekly...
2
by: Ian | last post by:
I have an Access 2000 database written in the UK, this database has many fields set to Currency, I now want to move this database over to a user in the USA but my Currency fields still show the UK...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.