Currency format

[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.

 

[fredg]

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),"$#")

 

[Al Campagna]

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."

 

[Guest]

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

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),"$#")

 

[Guest]

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.

 

[fredg]

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

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),"$#")

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

 

[Guest]

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!

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

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),"$#")

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

 

[fredg]

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.

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]

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.

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:

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

 

[Guest]

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!

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.

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:

 

[Guest]

Success with the 'ole IIf!

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

Thank you!

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.

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:

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