If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
problem getting currency formatting
I've got a calculated field in a query that represents a currency amount,
but I can't get it to display the dollar sign in either the query datasheet or in a form control. Even though I've tried to set the format property to "Currency" in both the query design and form design views. What I've also noticed in both of those design view is that in the drop box for the format property, sometimes the different choices like "General Number, Currency, Euro, Fixed, etc." are available in the drop box, and sometimes they're not. And even when they're available, and I select Currency, the dollar sign isn't visible in either the query or form. Again, this is a calculated field in the query, so there's no issue about the data type or property settings in the underlying table, since it's not a field in any table. Can anyone tell me what's going on with this, and how I can get this currency value to display dollar signs in the query datasheet and the form controls? Thanks in advance, Paul |
#2
|
|||
|
|||
problem getting currency formatting
On Fri, 9 Jul 2004 10:58:57 -0700, Paul James wrote:
I've got a calculated field in a query that represents a currency amount, but I can't get it to display the dollar sign in either the query datasheet or in a form control. Even though I've tried to set the format property to "Currency" in both the query design and form design views. What I've also noticed in both of those design view is that in the drop box for the format property, sometimes the different choices like "General Number, Currency, Euro, Fixed, etc." are available in the drop box, and sometimes they're not. And even when they're available, and I select Currency, the dollar sign isn't visible in either the query or form. Again, this is a calculated field in the query, so there's no issue about the data type or property settings in the underlying table, since it's not a field in any table. Can anyone tell me what's going on with this, and how I can get this currency value to display dollar signs in the query datasheet and the form controls? Thanks in advance, Paul Format the field with the calculation. In a query: Exp:Format([Quantity]*[Price],"Currency") or... Exp:Format([Quantity]*[Price],"$ #.00") In the form (or in a report): =Format([Quantity]*[Price],"Currency") or.. =Format([Quantity]*[Price],"$ #.00") -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#3
|
|||
|
|||
problem getting currency formatting
Thanks for the reply, Fred.
I tried your suggestions, and got partial success. Maybe you can tell me why I'm still having trouble with this. I did get the field to display a dollar sign in the query, by using the following expression: Total: Format(DSum("[Amount]","[tblDetail]","Invoice_ID=" & [InvoiceID]),"Currency") However, in the form control I used both =Format([Total],"$ #.00") and =Format([Total],"Currency") and in both cases the value displays "#Error." Another anomaly I've noticed about this field is that even though it displays the dollar signs in the query, all the values are left-aligned in the query datasheet, as if the query thinks it's a text field. Any idea why this is happening? |
#4
|
|||
|
|||
Correction
Actually, Fred's suggestions are working better than I thought. I just
closed and reopened the form, and I've noticed that the currency formatting in the query is now passing through to the form control. And that's without putting and number display type formatting directly in the form control. It did also inherit the left-justification from the query, but of course I was able to right justify the value in the form control with the formatting tool in design view. So the only remaining question I would have is why the field in the query is left-aligned even though it has a currency format. Is there anything I can to to make that field right-align in the query datasheet? Thanks again in advance. And thanks for providing these solutions, Fred. |
#5
|
|||
|
|||
problem getting currency formatting
On Fri, 9 Jul 2004 11:51:50 -0700, Paul James wrote:
Thanks for the reply, Fred. ** SNIPPED ** However, in the form control I used both =Format([Total],"$ #.00") and =Format([Total],"Currency") and in both cases the value displays "#Error." Probably because the name of the control is the same as the Field Name "Total" used in it's control source expression. Just change the control name. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#6
|
|||
|
|||
problem getting currency formatting
I would go into the actual table design and make sure the
field is setup as currency. I have had problems where it seems as if you have to set up the parameter everywhere. You would think if it were in the actual table design it would filter through, but sometimes it does not. Also you might want to set the decimal places to 2 rather than automatic, to eliminate some rounding issues. Hope this helps -----Original Message----- I've got a calculated field in a query that represents a currency amount, but I can't get it to display the dollar sign in either the query datasheet or in a form control. Even though I've tried to set the format property to "Currency" in both the query design and form design views. What I've also noticed in both of those design view is that in the drop box for the format property, sometimes the different choices like "General Number, Currency, Euro, Fixed, etc." are available in the drop box, and sometimes they're not. And even when they're available, and I select Currency, the dollar sign isn't visible in either the query or form. Again, this is a calculated field in the query, so there's no issue about the data type or property settings in the underlying table, since it's not a field in any table. Can anyone tell me what's going on with this, and how I can get this currency value to display dollar signs in the query datasheet and the form controls? Thanks in advance, Paul . |
#7
|
|||
|
|||
problem getting currency formatting
Thanks for your response to my question, Pat.
This field doesn't exist in a table. It's a calculated field that originates in a query. I did try to set the format property as currency in the Properties dialog for the query field, but as I mentioned, sometimes format choices such as General Number, Fixed and Currency appear in the format drop box, and other times that box is empty. I'd sure like to know why that happens. "Pat" wrote in message ... I would go into the actual table design and make sure the field is setup as currency. I have had problems where it seems as if you have to set up the parameter everywhere. You would think if it were in the actual table design it would filter through, but sometimes it does not. Also you might want to set the decimal places to 2 rather than automatic, to eliminate some rounding issues. Hope this helps -----Original Message----- I've got a calculated field in a query that represents a currency amount, but I can't get it to display the dollar sign in either the query datasheet or in a form control. Even though I've tried to set the format property to "Currency" in both the query design and form design views. What I've also noticed in both of those design view is that in the drop box for the format property, sometimes the different choices like "General Number, Currency, Euro, Fixed, etc." are available in the drop box, and sometimes they're not. And even when they're available, and I select Currency, the dollar sign isn't visible in either the query or form. Again, this is a calculated field in the query, so there's no issue about the data type or property settings in the underlying table, since it's not a field in any table. Can anyone tell me what's going on with this, and how I can get this currency value to display dollar signs in the query datasheet and the form controls? Thanks in advance, Paul . |
#8
|
|||
|
|||
problem getting currency formatting
Oh yes, I forgot about that. I guess Access gets confused if there's
redundant names. Thanks, Fred. |
#9
|
|||
|
|||
problem getting currency formatting
Paul, see:
Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html The crucial aspect is to explicitly typecast the calculation with CCur(). That will fail if the result is Null, so you will need something like this in the Field row of your query: MyCalcField: CCur(Nz(..., 0)) That yields a Currency type field, so you should be able to do anything with that, including using Currency in the Format property of the text boxes on your report, summing in the report, and so on. That's different from using Format() in the query, as this generates a string value which includes the formatting look, but may not work arithmetically as it is a string. -- 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. "Paul James" wrote in message ... I've got a calculated field in a query that represents a currency amount, but I can't get it to display the dollar sign in either the query datasheet or in a form control. Even though I've tried to set the format property to "Currency" in both the query design and form design views. What I've also noticed in both of those design view is that in the drop box for the format property, sometimes the different choices like "General Number, Currency, Euro, Fixed, etc." are available in the drop box, and sometimes they're not. And even when they're available, and I select Currency, the dollar sign isn't visible in either the query or form. Again, this is a calculated field in the query, so there's no issue about the data type or property settings in the underlying table, since it's not a field in any table. Can anyone tell me what's going on with this, and how I can get this currency value to display dollar signs in the query datasheet and the form controls? |
#10
|
|||
|
|||
problem getting currency formatting
Thanks again, Allen.
I didn't realize that Format() returned a string that looks like a number. That explains the strange behavior I experienced. I wrapped it with CCur(Nz(..., 0)) and now it's working just fine. I checked out the web page you referenced - another one of your clear explanations with some useful information. If only Microsoft's online help in Access were as good. G'day. Paul |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Currency Symbol Problem - Costa Rican Colon | SteveT | General Discussion | 0 | June 30th, 2004 09:50 PM |
formatting individual columns for different languages, including double-byte characters | Sandra Rosenzweig | General Discussion | 1 | June 20th, 2004 01:08 PM |
Formatting to Currency does not get applied | General Discussion | 5 | June 15th, 2004 11:17 PM | |
Conditional formatting problem | FW | Worksheet Functions | 2 | May 26th, 2004 02:59 PM |
Problem formatting cells as currency | Laura G | Worksheet Functions | 1 | January 2nd, 2004 02:24 PM |