problem getting currency formatting

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

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
 
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
Click to expand...

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")
 
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?
 
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.
 
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."
Click to expand...
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.
 
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
 
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.
 
Oh yes, I forgot about that. I guess Access gets confused if there's
redundant names.

Thanks, Fred.
 
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.
 
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
 
Back
Top