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

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

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

"Ken Sheridan" wrote:

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

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

On May 4, 3:35 pm, PPCO <P...@discussions.microsoft.com> wrote:
> 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"))
>
> "Ken Sheridan" wrote:
> > 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
>
> > "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"))

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!

"(E-Mail Removed)" wrote:

> 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
>
> On May 4, 3:35 pm, PPCO <P...@discussions.microsoft.com> wrote:
> > 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"))
> >
> > "Ken Sheridan" wrote:
> > > 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"))
>
>

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!

"(E-Mail Removed)" wrote:

> 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
>
> On May 4, 3:35 pm, PPCO <P...@discussions.microsoft.com> wrote:
> > 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"))
> >
> > "Ken Sheridan" wrote:
> > > 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"))
>
>

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

On May 4, 7:04 pm, PPCO <P...@discussions.microsoft.com> wrote:
> 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!
>
> "k...@ksheridan.orangehome.co.uk" wrote:
> > 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
>
> > On May 4, 3:35 pm, PPCO <P...@discussions.microsoft.com> wrote:
> > > 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"))
>
> > > "Ken Sheridan" wrote:
> > > > 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
>
> > > > "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"))

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

"(E-Mail Removed)" wrote:

> 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
>
> On May 4, 7:04 pm, PPCO <P...@discussions.microsoft.com> wrote:
> > 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!
> >
> > "k...@ksheridan.orangehome.co.uk" wrote:
> > > 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
> >
> > > On May 4, 3:35 pm, PPCO <P...@discussions.microsoft.com> wrote:
> > > > 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"))
> >
> > > > "Ken Sheridan" wrote:
> > > > > 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"))
>
>