Using expression builder to format a report field

[AJ]

Is there a way to use expression builder on a report to format a field to
currency?
I have a field on a report that can be alphanumeric or numeric. If it is
numeric I would like to format the data to currency (commas and $). Currently
this report will not let me do this in the normal means because as I
mentioned, it can be alphanumeric so I cannot jsut chnage the property to
currency.

Can this be done?

 

[Dirk Goldgar]

Is there a way to use expression builder on a report to format a field to
currency?
I have a field on a report that can be alphanumeric or numeric. If it is
numeric I would like to format the data to currency (commas and $).
Currently
this report will not let me do this in the normal means because as I
mentioned, it can be alphanumeric so I cannot jsut chnage the property to
currency.

Can this be done?

You might use a text box with something like this as its controlsource:

=IIf(IsNumeric([YourField]),
Format([YourField], "currency"),
[YourField])

Note that I wrote that on three lines for clarity, but it would really all
be on one line.

There are two possible issues that I can foresee with this approach. One is
that the text box must not be named the same as the field itself. That's a
common mistake when you take a control that was originally bound directly to
a field, and change it to be bound to an expression. But a control must not
have the same name as a field in the report's recordsource unless it's bound
directly to that field.

The second is that the IsNumeric() function may interpret certain types of
alphanumeric values as numeric. That's because it accepts exponential
notation involving the exponent signifiers "E" and "D". You can demonstrate
this in the Immediate Window:

?IsNumeric("123")
True
?IsNumeric("DEF")
False
?IsNumeric("1C3")
False
?IsNumeric("1D3")
True
?IsNumeric("1E3")
True
?IsNumeric("1F3")
False

So whether you can safely use IsNumeric to decide whether the value is a
number or not will depend on whether it will contain alphanumeric values of
the form #D# or #E#.

If you can't safely use IsNumeric, you may be able to use the Like operator
to determine whether to apply the currency format. For example:

=IIf([YourField] Like "*[A-Z]*",
[YourField],
Format([YourField], "currency"))

or

=IIf([YourField] Like "*[!0-9]*",
[YourField],
Format([YourField], "currency"))

But note that those simple patterns don't take signs (+, -) or decimal
points into account.

 

[AJ]

Thanks Dirk -
Can you also format the currency to not display decimals. Again, it does not
work in the normal "format" property field.

Is there a way to use expression builder on a report to format a field to
currency?
I have a field on a report that can be alphanumeric or numeric. If it is
numeric I would like to format the data to currency (commas and $).
Currently
this report will not let me do this in the normal means because as I
mentioned, it can be alphanumeric so I cannot jsut chnage the property to
currency.

Can this be done?

You might use a text box with something like this as its controlsource:

=IIf(IsNumeric([YourField]),
Format([YourField], "currency"),
[YourField])

Note that I wrote that on three lines for clarity, but it would really all
be on one line.

There are two possible issues that I can foresee with this approach. One is
that the text box must not be named the same as the field itself. That's a
common mistake when you take a control that was originally bound directly to
a field, and change it to be bound to an expression. But a control must not
have the same name as a field in the report's recordsource unless it's bound
directly to that field.

The second is that the IsNumeric() function may interpret certain types of
alphanumeric values as numeric. That's because it accepts exponential
notation involving the exponent signifiers "E" and "D". You can demonstrate
this in the Immediate Window:

?IsNumeric("123")
True
?IsNumeric("DEF")
False
?IsNumeric("1C3")
False
?IsNumeric("1D3")
True
?IsNumeric("1E3")
True
?IsNumeric("1F3")
False

So whether you can safely use IsNumeric to decide whether the value is a
number or not will depend on whether it will contain alphanumeric values of
the form #D# or #E#.

If you can't safely use IsNumeric, you may be able to use the Like operator
to determine whether to apply the currency format. For example:

=IIf([YourField] Like "*[A-Z]*",
[YourField],
Format([YourField], "currency"))

or

=IIf([YourField] Like "*[!0-9]*",
[YourField],
Format([YourField], "currency"))

But note that those simple patterns don't take signs (+, -) or decimal
points into account.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk -
Can you also format the currency to not display decimals. Again, it does
not
work in the normal "format" property field.

How about this:

=IIf(IsNumeric([YourField]), Format([YourField], "$#,##0"), [YourField])

?

 

[Tony Toews [MVP]]

I have a field on a report that can be alphanumeric or numeric.

What kind of field is this? I wonder if you wouldn't be far better
off in the long term creating two fields. One for the alpha data and
the other for the currency data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/