text in a currency field

[Guest]

how can I get a currency field to accept text?
In a budget column(field) there are instances where no $$ was budgeted and I
wish to show a zero .... a simple zero. in some instances the data is not
yet available and I wish to show NA.

somebody tried make a null value show NA but then no zero and the numbers
were just that numbers .. no currency.

how can I do what I want?

 

[Joseph Meehan]

how can I get a currency field to accept text?
In a budget column(field) there are instances where no $$ was
budgeted and I wish to show a zero .... a simple zero. in some
instances the data is not yet available and I wish to show NA.

somebody tried make a null value show NA but then no zero and the
numbers were just that numbers .. no currency.

how can I do what I want?

If I understand the problem, and I am not sure I do, how about leaving
it as null and or zero and then in the form or report create a field that
will show N/A for the zero or null as you wish and show the actual value if
there is a value.

Notice, I have not tried this but if it works, I claim all rights to it.

 

[Brendan Reynolds]

A Currency field can never contain text, but you can display text in place
of Null values and still format non-Null values as currency. For example, in
a query ...

SELECT IIf([TestCur] Is Null,"N/A",Format$([TestCur],"Currency")) AS Expr1
FROM tblTest;

 

[John Vinson]

how can I get a currency field to accept text?
In a budget column(field) there are instances where no $$ was budgeted and I
wish to show a zero .... a simple zero. in some instances the data is not
yet available and I wish to show NA.

somebody tried make a null value show NA but then no zero and the numbers
were just that numbers .. no currency.

YOu cannot store text in a Currency datatype, but you can set its
Format property: a numeric datatype format allows FOUR formats, which
apply to positive, negative, zero and NULL respectively. Try setting
the format of the field to

$#,##0.00;($#,##0.00)[Red];"0";"N/A"


John W. Vinson[MVP]

 

[Guest]

John,

thanks you, this seems to come the closest to what I'm trying to do. when
there is an actual dollar amount the figure appears one space away from the
right margin of the field. How can I get the amounts to right align as the
N/A and 0 fields do?

they may want the 0 to have $ in front of it .. I tried putting a $in front
of the 0 in your custom format ..... and it worked but in those fields the $0
was now two spaces from the right margin of the field box?

any ideas for a solution?

how can I get a currency field to accept text?
In a budget column(field) there are instances where no $$ was budgeted and I
wish to show a zero .... a simple zero. in some instances the data is not
yet available and I wish to show NA.

somebody tried make a null value show NA but then no zero and the numbers
were just that numbers .. no currency.

YOu cannot store text in a Currency datatype, but you can set its
Format property: a numeric datatype format allows FOUR formats, which
apply to positive, negative, zero and NULL respectively. Try setting
the format of the field to

$#,##0.00;($#,##0.00)[Red];"0";"N/A"


John W. Vinson[MVP]

 

[John Vinson]

John,

thanks you, this seems to come the closest to what I'm trying to do. when
there is an actual dollar amount the figure appears one space away from the
right margin of the field. How can I get the amounts to right align as the
N/A and 0 fields do?

they may want the 0 to have $ in front of it .. I tried putting a $in front
of the 0 in your custom format ..... and it worked but in those fields the $0
was now two spaces from the right margin of the field box?

any ideas for a solution?

Just play around with the formats. The blank is to allow for a plus
sign - you may need to put an explicit blank in the other formats.

John W. Vinson[MVP]