Conditional Decimal Places

[PPCO]

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:

Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))

or

format(x,iif(x>50,"0","0.00"))

 

[Ken Sheridan]

One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

 

[PPCO]

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))

One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:

Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))

or

format(x,iif(x>50,"0","0.00"))

 

[ken]

Firstly it sounds like you've entered it directly in the properties
sheet, not in the event procedure. This is how its done:

Select the relevant section in report design view and open its
properties sheet if its not already open. Then select the On Format
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

But as well as that you've used the IIf function not an If….End If
construct. The code would be:

If Me.[GoalResults] >= 50 Then
Me.[txtGoalResults] = Format([GoalResults],"#")
Else
Me.[txtGoalResults] = Format([GoalResults],"#.00")
End If

Ken Sheridan
Stafford, England

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))

One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assigna
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:
Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))
or
format(x,iif(x>50,"0","0.00"))

 

[PPCO]

I'd had the iif statement in the on format event line but hadn't used the
code builder or a straight if statement. So I did that, and its giving me the
error: "Run-Time Error '-2147352567 (80020009)'
You can't assign a value to this object"

Thanks so much for your help!

Firstly it sounds like you've entered it directly in the properties
sheet, not in the event procedure. This is how its done:

Select the relevant section in report design view and open its
properties sheet if its not already open. Then select the On Format
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

But as well as that you've used the IIf function not an If….End If
construct. The code would be:

If Me.[GoalResults] >= 50 Then
Me.[txtGoalResults] = Format([GoalResults],"#")
Else
Me.[txtGoalResults] = Format([GoalResults],"#.00")
End If

Ken Sheridan
Stafford, England

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))

One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

"PPCO" wrote:

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:

Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))

or

format(x,iif(x>50,"0","0.00"))

 

[PPCO]

I'd had the iif statement in the on format event line but hadn't used the
code builder or a straight if statement. So I did that, and its giving me the
error: "Run-Time Error '-2147352567 (80020009)'
You can't assign a value to this object"

Thanks so much for your help!

Firstly it sounds like you've entered it directly in the properties
sheet, not in the event procedure. This is how its done:

Select the relevant section in report design view and open its
properties sheet if its not already open. Then select the On Format
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

But as well as that you've used the IIf function not an If….End If
construct. The code would be:

If Me.[GoalResults] >= 50 Then
Me.[txtGoalResults] = Format([GoalResults],"#")
Else
Me.[txtGoalResults] = Format([GoalResults],"#.00")
End If

Ken Sheridan
Stafford, England

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))

One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

"PPCO" wrote:

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:

Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))

or

format(x,iif(x>50,"0","0.00"))

 

[ken]

My guess would be that the txtGoalResults control is either bound to
the GoalResults field, i.e. has the field name as its ControlSource
property, or it’s a computed control with some expression as its
ControlSource property. What you should have is:

1. A control named GoalResults bound to the GoalResults field, and
with its Visible property set to False (No).

2. A control named txtGoalResults with a blank ControlSource property
and its Visible property set to True (Yes).

Ken Sheridan
Stafford, England

I'd had the iif statement in the on format event line but hadn't used the
code builder or a straight if statement. So I did that, and its giving methe
error: "Run-Time Error '-2147352567 (80020009)'
You can't assign a value to this object"

Thanks so much for your help!

Firstly it sounds like you've entered it directly in the properties
sheet, not in the event procedure. This is how its done:

Select the relevant section in report design view and open its
properties sheet if its not already open. Then select the On Format
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

But as well as that you've used the IIf function not an If….End If
construct. The code would be:

If Me.[GoalResults] >= 50 Then
Me.[txtGoalResults] = Format([GoalResults],"#")
Else
Me.[txtGoalResults] = Format([GoalResults],"#.00")
End If

Ken Sheridan
Stafford, England

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))
:
One possible reason for it not working would be if you've named theunbound
text box control the same as the underlying field name, which wouldintroduce
a circular reference. If so change the control's name to txtFieldname or
similar.
Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:
If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If
Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.
Ken Sheridan
Stafford, England
:
I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:
Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))
or
format(x,iif(x>50,"0","0.00"))

 

[PPCO]

You're good--that's what it was. Don't know how that happened, but I must
have mistakenly done that earlier. Thanks!

My guess would be that the txtGoalResults control is either bound to
the GoalResults field, i.e. has the field name as its ControlSource
property, or it’s a computed control with some expression as its
ControlSource property. What you should have is:

1. A control named GoalResults bound to the GoalResults field, and
with its Visible property set to False (No).

2. A control named txtGoalResults with a blank ControlSource property
and its Visible property set to True (Yes).

Ken Sheridan
Stafford, England

I'd had the iif statement in the on format event line but hadn't used the
code builder or a straight if statement. So I did that, and its giving me the
error: "Run-Time Error '-2147352567 (80020009)'
You can't assign a value to this object"

Thanks so much for your help!

Firstly it sounds like you've entered it directly in the properties
sheet, not in the event procedure. This is how its done:

Select the relevant section in report design view and open its
properties sheet if its not already open. Then select the On Format
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

But as well as that you've used the IIf function not an If….End If
construct. The code would be:

If Me.[GoalResults] >= 50 Then
Me.[txtGoalResults] = Format([GoalResults],"#")
Else
Me.[txtGoalResults] = Format([GoalResults],"#.00")
End If

Ken Sheridan
Stafford, England

So I tried that approach, and its giving me the error: "Microsoft Access
can't find the macro:..." And it lists my code which is below. Something
wrong with my
code?
IIf([GoalResults]>=50, [txtgoalresults]=Format([goalresults],"#"),
[txtgoalresults]=Format([goalresults],"#.00"))

:
One possible reason for it not working would be if you've named the unbound
text box control the same as the underlying field name, which would introduce
a circular reference. If so change the control's name to txtFieldname or
similar.

Another approach would be to use a hidden bound control FieldName, and in
the Format event procedure of whatever section the control is in assign a
value to a visible unbound control with:

If Me.[FieldName] >= 50 Then
Me.[txtFieldName] = Format([FieldName],"#")
Else
Me.[txtFieldName] = Format([FieldName],"#.00")
End If

Note that you have to have the hidden bound control in the report for this
as, unlike in a form's module, you cannot reference a column in a report's
underlying table or query directly in a report's module.

Ken Sheridan
Stafford, England

"PPCO" wrote:

I wanted to see if there was a way on a report or in a query to have numbers
50 or larger have no decimal places and numbers under 50 to show 2 decimal
places. I've posted this question before and was told to do this but it never
worked:

Using an unbound control, try:
=IIf([FieldName]>=50,Format([FieldName],"#"),Format([FieldName],"#.00"))

or

format(x,iif(x>50,"0","0.00"))