Currency Format Lost in Union Query

Thanks to John Spencer, I resolved one problem in my union query (Thank you, 
John). But I have another problem. Some of my fields in my union query are 
currencies and others percents. I see both formats are lost. I am sure it is 
a union query problem, because when I ran an individual query, none of the 
formats were lost. Is there any way I can keep currency and percent formats 
without writing, FORMATCURRENCY, etc for each field?
Thank you.
0
Utf
5/27/2010 9:26:56 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1962 Views

Similar Articles

[PageSpeed] 8

What data types are these fields?

The field in a UNION query will normally take on the data type of the field 
in the first SELECT. So, if the field in the first SELECT statement of the 
UNION is of type Currency, the output field would most likely be of type 
Currency as well, unless something else is going on (e.g. if it's a 
calculated expression, or if there are many Nulls, or ...)

Since you can't use Design view, you cannot set the Format property with the 
Properties box, but it is possible to set it programmaticallly. You could 
use the SetPropertyDAO() function from this page:
    http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO
like this:
    call SetPropertyDAO(CurrentDb.QueryDefs("MyQuery").Fields("MyField"), 
"Format", dbText, "Currency")

In the end, the Format doesn't matter. It's purely a display thing, and you 
generally don't expose the table or query itself to an end user. The more 
important thing is the data type of the field, and setting the Format 
property won't achieve that. To test the data type, use something like this 
in the Immediate Window:
    ? CurrentDb.QueryDefs("MyQuery").Fields("MyField").type
The result will be one of the numbes from the DAO column on this page:
    http://allenbrowne.com/ser-49.html
or you can use the FieldTypeName() function here:
    http://allenbrowne.com/func-06.html

HTH

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Jill" <Jill@discussions.microsoft.com> wrote in message 
news:793226F2-D606-4CC3-9297-13D47FCDF2CF@microsoft.com...
> Thanks to John Spencer, I resolved one problem in my union query (Thank 
> you,
> John). But I have another problem. Some of my fields in my union query are
> currencies and others percents. I see both formats are lost. I am sure it 
> is
> a union query problem, because when I ran an individual query, none of the
> formats were lost. Is there any way I can keep currency and percent 
> formats
> without writing, FORMATCURRENCY, etc for each field?
> Thank you. 

0
Allen
5/28/2010 2:42:00 AM
Reply:

Similar Artilces:

Money 2005 Currency Goof-Up
Friends A word of warning! Never attempt to use Money 2005, If you have transactions or investments in international currencies. In 2005, Money gives you the option of choosing their in-built currency in place of any custom currency we have created. But, this is implemented defectively. 1. Money 2005's list of currencies for converting "Custom Currencies" is different from the full list of currencies, which it displays after the file has been converted. For examply, the currencies "UAE Dhiram" and also "Indian Rupee" do not appear in the list di...

Query from 3 tables
I have three tables that I want to put in query. But this is what I need: Articles Stores MinimumQTY ----------- ---------- --------------------- ArticleID StoreID ArticleID ArticleName StoreName StoreID ArticlePrice MinQTY OptimalQTY What I would like is to have a query that shows all recors from Articles, and for each article to show all Stores and for each StoreID to show records in MinimumQTY...

Criteria in a calculated query field
Hi, I have the below formula in the query field and run fine. Expiration: (([shelf Life (Months)]/12*365)+[Date]) But when I write the criteria below and not not get the data I want Between [enter start date] And [enter end date] Example: when I run just the formula I get for example dates in April 2010. But when I use the criteria and ask for data between 04/01/10 and 04/30/10 I get blank results. What am I doing wrong? Thank you for your help. Rubem On Sun, 18 Apr 2010 17:09:01 -0700, Rubem <Rubem@discussions.microsoft.com> wrote: >Hi, > >...

Currency symbol inccorect
Hi If i click the currency button I get 7,928.00 $ If i manualy format the cell I can get the correct correct currency R 7,928.00 I have the correct settings in regional settings in the control panel. I have to set the symbol everytime I format manually. How do I set it that the currency button uses the correct formatting? Hi ceaza! It still seems like you have a Regional settings problem. Make sure on the currency tab you select exactly what you want. Then click "Apply" and not OK I recall that in some cases a shut down and re-boot was necessary before they took effect eve...

format and decimals in control text
Hi format and decimals in a text box do not seem to work as expected. I can set format to general number or leave it blank and decimals to 0 or 1, makes no difference, I get 1 decimal in some text boxes and no decimals in others. the decimals are not rounded off either, they can be .3 or .9. does anyone know why and how to deal with this problem? regards LP wrote: >format and decimals in a text box do not seem to work as expected. >I can set format to general number or leave it blank and decimals to 0 or 1, >makes no difference, I get 1 decimal in some text boxes and no decimals...

Subform-Query Problems
I have read thru many other threads to try and find a solution to my problem but to no avail. My issue: I have two tables that I am trying to link so that my 2d table data can be used as a subform on the main form for which all the data on the 1st table is stored. What I am trying to do with the subform is to have a datasheet that shows the date of visit..type of 8823 issued..etc but to correspond to the PROJECT for which the visit is for in the Main Form. I get the message below.. I have tried to change my query and I have no idea what I am doing wrong. I have Project Name as my Primar...

RE: Query in a form
I have created a database to use at work for keeping record of orders, shipping details etc. This is to assist in prediciting cashflow etc. My problem is, I have created a form for Indirect shipments and a form for direct shipments, which the information for both is linked from the same tables. Is there a way I can create a control source so that when I go into the Direct form it does not show the Indirect, as the way I know would be to create a query and then create the form from the query, which would mean almost staring all over again, which I don't wnat to do, nor have the time. Gr...

auto format chart colours
Is it possible to have a bar chart where all values greater than 100 are automatically coloured red, less than 100 are coloured green? thanks Yes, have a look at Jon's examples of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy bobf wrote: > Is it possible to have a bar chart where all values > greater than 100 are automatically coloured red, less than > 100 are coloured green? > > thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Query Help 02-14-08
I have a query that pulls usage volumes, this works fine. The catch is I have a list of all of the prices that that item has ever had (price increases and decreases) i.e. Item #300500 prices would look like this 1/1/1999 $4.50 3/4/2000 $ 4.10 7/8/2003 $ 5.56 11/1/2006 $ 5.20 1/1/2008 $ 6.00 I have a date parameter in the query that a person can program when they would like to pull the from i.e. between 1/1/2007 and 12/31/2007. is there a way to have it where what ever date they put into the datefield that it'll pull the last date applying to that range? So it would pull 11/1/2006 since...

How to apply formatting to Excel with existing external connection ranges?
Hi, I used Advanced Find in MSCRM and exported the results in Dynamic Excel file. Now, I wish to format the list using Excel's 'Format as Table' in Home ribbon, but it warns me that an overlapping external data ranges occurred and it will be converted to a table and remove all external connections. How will avoid this? We wish to format the list and maintain the external connections from CRM. Please advise. ...

Creating a Formula to Format Column automatically? #6
What if I wanted to do more than three conditions -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 You can have 4 conditions. 1. The default 2,3,4 other conditions. More than that would require Code, most likely Event code. Example.... Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = I...

Web query and special characters
Hello, I have a simple HTML data dump which has special characters: ������ In the HTML source I placed <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> When I download this table, all the accents are translated into unprintable characters. I know there are macros which remove these unprintable characters, but then the word in uncomplete c�digo gets downloaded as cädigo and cleaned up it turns into cdigo. Any help would be apreciated. Thanks This was fixed by changing the meta tag to <meta HTTP-EQUIV="content-type" CONTE...

Need a report or query(?)
I have a table of class information that contains class name, start date, instructor, start time and days held - the days held (if more than one day) repeat in separate records. I need to end up with one record per class that shows class name, instructor, start date and the days and times held. (Also in the mix is that times held can be different each day held.) Where do I start? A query? Two queries? I started by creating a table and grouping on class name so I have a table with non-dup class names...now, I am dead in the water! Thanks in advance! On Tue, 18 May 2010 0...

Formatting cells as degrees?
I want to add two columns to a spreadsheet with the latitude and longitude of the location, either in the format: DEGREES MINUTES SECONDS or DEGREES MINUTES.DECIMINAL-MINUTES How can I format the cells like this? -- Nigel M Hi Nigel, Look here: http://www.cpearson.com/excel/latlong.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Nigel Molesworth" <reply@thegroup.com> wrote in message news:n4k763l29mj5v0qhl3ugbtmgc58mo7c845@4ax.com... |I want to add two columns to a spreadsheet with the latitude and | longitude of the location, either in the format: | | DEGREES...

Form parameter query
I'm hoping someone can help me with this . I'm trying to get my query to use a form to get the query parameters. When I run the query, it displays the text "[Forms]![frmEE]![cboEE]" as a prompt rather than displaying the form where the user can make a selection. I'm using the build wizard to construct the following query: SELECT Employees.* FROM Employees WHERE (((Employees.LastName)=[Forms]![frmEE]![cboEE])); -- Any and all contributions are greatly appreciated ... Best Regards A couple possibilities... First, I'll assume that (Employees.LastName) is a t...

Recordsets as Query Sources?
I have a database with a number of M2M relationships, which I have gathered together in a very long query which I use to build an ADO recordset for one "record". Is there any way I can run queries on that recordset? I want to remove duplicate entries in specific fields. Are the M2M tables linked into your database? If so, have you considered just creating a select query in Access, rather than an ADO recordset? Yu could then use the query as the record source. "David Wetmore" wrote: > I have a database with a number of M2M relationships, which I have gathered toget...

Change multiple file type from 2003 to 2007 format
I have a lot of templets and there is someplace you can highlight 8 to 10 and and do a "Save As" and you could change from 2003 to 2007. I can not remember how!! Help Thanks ...

FRX row format, MDA codes should allow me to use wild card charac.
FRX row format, MDA codes should allow me to use wild card characters, such as 11-1100-00,[Group.CODE???] 11-1100-00,[Group.CODE001]+11-1100-00,[Group.CODE002] -- Regards, Raja Raja, When using MDA Group and/or MDA Code within FRX you must explicitly enter the code IN FULL Use of wildcards is not supported, nor permitted. You will have to list each Code individually as you illustrate in your second line. Robert "Raja V" <rvijayarangan@hotmail.com> wrote in message news:84D34C61-13BC-42DF-B393-3F16E3E9C151@microsoft.com... > FRX row format, MDA codes should allow me to...

"Overflow" message in UNION query
Hi, I have a UNION query that has started to generate an "Overflow" error message when I execute it. The SQL is "select * from query1 union select * from query2". Both "query1" and "query2" execute fine when I try them separately - I only get the "Overflow" in the union query. Both "query1" and "query2" have identical fields (20 of them) in identical formats - the data is just sourced from two distinct tables. Only "query1" has a calculation to add two numbers together, so I can't see anything likely to...

System Date Wrong Format
Hello, I just did a new install of GP onto my desktop and the date format is different from the date format on my laptop. On my laptop, and on 95% of the company's systems, the date is displayed as MM/DD/YYYY. However, on this new install, and on another user's laptop, the date is displaying as DD MM YY. Any idea how to change this? I've looked through a bunch of settings, but didn't see anything. I also double checked the system date on my laptop and PC and they're the same. Any ideas? Thanks so much for your help! Alyssa I don't know if this is what'...

auto date: cond. format Question
have code for auto date entered to cells, but need to tweak conditional format to change color for new item today, tried "=" sign, <now(), but can't get. using: =IF(AF9="","",(AF9+AF$7)<TODAY()) to get x day old change color 1. trying to flip to get to change color if new today 2. is there a way to exclude weekends (sat/sun) in x days old, for conditional format, so don't have to use absolute cell, to switch from 1 to 3 days?. thanks in advance. ...

How can I modify punctuations' formatting in Word 2007
I want NO SPACING between last text word and ; : ? or ! How can I modify desired spacing default in Word 2007? -- Sean You would get that space if Word thinks you're typing in French (and maybe some other languages). Be sure the language of your text is set to English (or whatever it should be). You can display the language in your Status Bar (the bottom frame of the document window) by right- clicking on the bar and choosing it from the list. To set the language for the whole document, select it all (Ctrl-A) and choose the language from that entry in the status bar. On ...

DateTime format for an attribute
I created a custom datetime attribute...it displays ok on the form, popping the calendar...however, I get the standard version with all the days on display...I would like to display a calendar only for months and years, is this possible? I am using this attribute on the Opportunity form...if a user has to enter a date for December 2007, he has to either type it (with possible typo errors) or scroll all along the calendar...with a month-year only calendar, the popup will be smaller. Thanks, GuilloTkx Hi Guillo, I don't think it's possible. Maybe the best you can do is using...

Changing text formats on a chart linked to a spreadsheet
Currently I have some text combined with formulae in a text box showing on a chart sheet linked to a worksheet where the original info is. In the chart sheet I would like to change the size of some of the text and add colour to some of the text as well. The text box can be changed if there is a better way to achieve the above. Any suggestions would be greatly appreciated. Roger If the textbox (or other text element) is linked to a cell, you cannot format individual characters within the text. What I have done is used multiple textboxes, one per required format, and grouped them ...

Contact Link Lost
Hi, This scenario has been created by migrating Public Folders to a new SBS server. (FYO. The old SBS server is fully decommisioned). We have successfully migrated all exchange data from the old server to the new by exporting to and importing from .pst files. Our sales team when creating new appointments have added contacts that are located in a public folder. Now when they drill down into the contact's details in any given appointment the link is lost. When I remove the contact from the contact field in the appointment and add it back I am able to drill into the contact's d...