formatting numbers to currency

[PJ]

I have a text box with numbers and words. I am trying to format the numbers
to currency and it will not allow me to because the output can also be a word.

Example:

100000000 needs to say $100,000,00.00 but I sometimes will have the word
Grandfathered In or Exception Approval or other words so I am getting a error
message.

Is it possible to format numbers to currency if there is a number or have
words output if no number?

Thank in advance.

 

[fredg]

I have a text box with numbers and words. I am trying to format the numbers
to currency and it will not allow me to because the output can also be a word.

Example:

100000000 needs to say $100,000,00.00 but I sometimes will have the word
Grandfathered In or Exception Approval or other words so I am getting a error
message.

Is it possible to format numbers to currency if there is a number or have
words output if no number?

Thank in advance.

You're database is not correctly set up.
You should not have different types of data in one field.

Add a new field to your table (currency datatype) to contain whatever
number value you need to show.
Then remove those number values from the existing field and put them
into this new field.

You should have 2 fields containing the separated data.

Now it is a very simple method to put them together on your report,
using an Unbound control:
=[MyTextField] & " " & Format([TheNewField],"currency")

 

[John Spencer]

If you cannot restructure your database, you can use an expression in a
query or as the control source of a textbox to do so. I would try the
following:

IIF(IsNumeric([SomeField]),Format(Val([SomeField]),"Currency"),[SomeField])

In rare instances you could get a spurious value - for instance if a
field value was 1e3 the above would return $1,000.00 instead of "1e3".

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================