Currency format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
 
Stephanie said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...

= Format([cboScreenCat].[Column](2),"$#")
 
Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."
 
Fred,
That's not working quite right- I get:
$ (rather than "0")
$
$20

fredg said:
Stephanie said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...

= Format([cboScreenCat].[Column](2),"$#")
Click to expand...
 
Al,
thanks for the tips.
Just to confirm, in the Properties > Format > Format, I would enter the line
you gave:
$# ; -$# ; 0 ; 0
This still returns: 0,0,20

$#,##0.00 ; -$#,##0.00 ; .00 ; .00
This returns: 0,0,20 as well...

Am I doing something incorrectly?


Al Campagna said:
Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."


Stephanie said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...
Click to expand...
 
Stephanie said:
Fred,
That's not working quite right- I get:
$ (rather than "0")
$
$20

fredg said:
Stephanie said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...

= Format([cboScreenCat].[Column](2),"$#")
Click to expand...
Click to expand...

Ah! I missed that you didn't want the $ if the amount was 0.

Set the control source back to:
=[cboScreenCat].[Column](2)

Then, as the Format property for this control, write:
$#;-$#;0

Look up Format property + Number and Currrency datatype

Fred
 
As if you miss much!
Thanks for the link- interesting and painful. The formatting is still:
0,15,20 with no $.
I'll keep trying.
Thanks!

fredg said:
Stephanie said:
Fred,
That's not working quite right- I get:
$ (rather than "0")
$
$20

fredg said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.

= Format([cboScreenCat].[Column](2),"$#")
Click to expand...
Click to expand...

Ah! I missed that you didn't want the $ if the amount was 0.

Set the control source back to:
=[cboScreenCat].[Column](2)

Then, as the Format property for this control, write:
$#;-$#;0

Look up Format property + Number and Currrency datatype

Fred
Click to expand...
 
Stephanie said:
Al,
thanks for the tips.
Just to confirm, in the Properties > Format > Format, I would enter the line
you gave:
$# ; -$# ; 0 ; 0
This still returns: 0,0,20

$#,##0.00 ; -$#,##0.00 ; .00 ; .00
This returns: 0,0,20 as well...

Am I doing something incorrectly?


Al Campagna said:
Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."


Stephanie said:
Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...
Click to expand...
Click to expand...

Stephanie,
I'm away from my computer for another few days, so I have no way to test my
replies.
I would suspect that the data in Column(2) is not a number datatype (even
though it may be a number) but text.

Try it this way:
As the cnontrol source of an unbound control:
 
fredg said:
Stephanie said:
Al,
thanks for the tips.
Just to confirm, in the Properties > Format > Format, I would enter the line
you gave:
$# ; -$# ; 0 ; 0
This still returns: 0,0,20

$#,##0.00 ; -$#,##0.00 ; .00 ; .00
This returns: 0,0,20 as well...

Am I doing something incorrectly?


Al Campagna said:
Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."


Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...
Click to expand...

Stephanie,
I'm away from my computer for another few days, so I have no way to test my
replies.
I would suspect that the data in Column(2) is not a number datatype (even
though it may be a number) but text.

Try it this way:
As the cnontrol source of an unbound control:
Click to expand...

Whoops... sent too soon.
First try ... as the control source of an Unbound control:
= IIf([cboScreenCat].[Column](2) =
0,0,Format([cboScreenCat].[Column](2),"$#"))

It also might be that the actual datatype of column(2) is not Number but
text.
If the above doesn't work, try:
= IIf([cboScreenCat].[Column](2) = "0","0","$" & [cboScreenCat].[Column](2))

If still no good, copy and paste the actual combo box Rowsource SQL and let
us see what's going on.
Let us know the datatype of the fields.

Fred
 
Thanks for hanging in there! I think you're correct- the combo box itself is
a text field.

You dropped off in the middle:

Try it this way:
As the control source of an unbound control: ...

a rapt audience!

fredg said:
Stephanie said:
Al,
thanks for the tips.
Just to confirm, in the Properties > Format > Format, I would enter the line
you gave:
$# ; -$# ; 0 ; 0
This still returns: 0,0,20

$#,##0.00 ; -$#,##0.00 ; .00 ; .00
This returns: 0,0,20 as well...

Am I doing something incorrectly?


Al Campagna said:
Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."


Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...
Click to expand...

Stephanie,
I'm away from my computer for another few days, so I have no way to test my
replies.
I would suspect that the data in Column(2) is not a number datatype (even
though it may be a number) but text.

Try it this way:
As the cnontrol source of an unbound control:
Click to expand...
 
Success with the 'ole IIf!

Control Source:
=IIf([cboScreenCat].[Column](2)=0,0,Format([cboScreenCat].[Column](2),"$#"))
Format: $#;-$#;0

Thank you!

fredg said:
fredg said:
Stephanie said:
Al,
thanks for the tips.
Just to confirm, in the Properties > Format > Format, I would enter the line
you gave:
$# ; -$# ; 0 ; 0
This still returns: 0,0,20

$#,##0.00 ; -$#,##0.00 ; .00 ; .00
This returns: 0,0,20 as well...

Am I doing something incorrectly?


:

Stephanie,
Currency format usually involves 2 decimal places.
For whole numbers...as you indicated, set the calculated field's Format
to...
$# ; -$# ; 0 ; 0

or in standard Currency... but no dollar sign for 0....
$#,##0.00 ; -$#,##0.00 ; .00 ; .00
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."


Hi.
I have a field in my form with this control source:

=[cboScreenCat].[Column](2)

which gives me:
0
15
20

I'd like to see:
0
$15
$20

Is there a way to get the "$" to appear if the value is not zero?

Thanks.
Click to expand...

Stephanie,
I'm away from my computer for another few days, so I have no way to test my
replies.
I would suspect that the data in Column(2) is not a number datatype (even
though it may be a number) but text.

Try it this way:
As the cnontrol source of an unbound control:
Click to expand...

Whoops... sent too soon.
First try ... as the control source of an Unbound control:
= IIf([cboScreenCat].[Column](2) =
0,0,Format([cboScreenCat].[Column](2),"$#"))

It also might be that the actual datatype of column(2) is not Number but
text.
If the above doesn't work, try:
= IIf([cboScreenCat].[Column](2) = "0","0","$" & [cboScreenCat].[Column](2))

If still no good, copy and paste the actual combo box Rowsource SQL and let
us see what's going on.
Let us know the datatype of the fields.

Fred
Click to expand...
 
Back
Top