IIF condition in report text box

[Ray Thigpen]

I have a report that opens in preview from a command button. I need the text
box to display 1 of 2 different fields, depending on the value of another
field within the report. The dependent field is [CropCode], which is either

244 value or <244. I cannot get the display field - which would either be

the value of [Acres] or [Trees], to work properly. I have tried putting a
"text" value dependent on the [CropCode] field and works fine, but cannot
pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[Allen Browne]

Put an expression such as this into the Control Source of your text box:
=IIf([CropCode] < 244, [Trees], [Acres])

I am not clear about which way around you want those value, so you may need
to swap the Trees and Acres over.

Make sure the Name property of the text box is not the same as any of the
field names in your report, e.g. it cannot be called CropCode, Trees, or
Acres.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either

244 value or <244. I cannot get the display field - which would either be

the value of [Acres] or [Trees], to work properly. I have tried putting a
"text" value dependent on the [CropCode] field and works fine, but cannot
pick up the value of the fileds.

 

[BruceM]

One way would be to base the report on a query. In query design view, add a
field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box on the
report to that field. Note that a value of exactly 244 will produce the
result "Trees".

Another option would be to assign the values in the report's Print event
(txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in such
cases, so I'm not all that sure about the second choice I offered.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either

244 value or <244. I cannot get the display field - which would either be

the value of [Acres] or [Trees], to work properly. I have tried putting a
"text" value dependent on the [CropCode] field and works fine, but cannot
pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[Ray Thigpen]

Thanks for the promt reply. However, I have tried exactly that -
=IIf([CropCode]<244,[Trees],[Acres]) - copied and pasted from teh control
source, but for some reason the [Acres] field will work, but the[Trees] will
not. The spelling is correct including capitalization of "T". The name of
the text box was not the same. It was "AcreTree", and I changed to
"AcreTreeFld" just to make sure, but the IIf statement will not return the
value of the field [Trees}. It only displays 0.0. Seems kind of crazy, but
is probably something simple that I can not figure out.

Put an expression such as this into the Control Source of your text box:
=IIf([CropCode] < 244, [Trees], [Acres])

I am not clear about which way around you want those value, so you may
need to swap the Trees and Acres over.

Make sure the Name property of the text box is not the same as any of the
field names in your report, e.g. it cannot be called CropCode, Trees, or
Acres.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either

244 value or <244. I cannot get the display field - which would either
be

the value of [Acres] or [Trees], to work properly. I have tried putting a
"text" value dependent on the [CropCode] field and works fine, but cannot
pick up the value of the fileds.

 

[Allen Browne]

Tempoarily add the Acress and Trees text boxes to the report.
Does it work now?
If so, just set the Visible property of the other 2 controls to No.
(Sometimes reports have trouble referring directly to a field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the promt reply. However, I have tried exactly that -
=IIf([CropCode]<244,[Trees],[Acres]) - copied and pasted from teh control
source, but for some reason the [Acres] field will work, but the[Trees]
will not. The spelling is correct including capitalization of "T". The
name of the text box was not the same. It was "AcreTree", and I changed to
"AcreTreeFld" just to make sure, but the IIf statement will not return the
value of the field [Trees}. It only displays 0.0. Seems kind of crazy, but
is probably something simple that I can not figure out.

Put an expression such as this into the Control Source of your text box:
=IIf([CropCode] < 244, [Trees], [Acres])

I am not clear about which way around you want those value, so you may
need to swap the Trees and Acres over.

Make sure the Name property of the text box is not the same as any of the
field names in your report, e.g. it cannot be called CropCode, Trees, or
Acres.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either
244 value or <244. I cannot get the display field - which would either
be
the value of [Acres] or [Trees], to work properly. I have tried putting
a "text" value dependent on the [CropCode] field and works fine, but
cannot pick up the value of the fileds.

 

[Ray Thigpen]

I added the text boxes and they display, but for some reason no matter which
way - < or > 244, only the [Acres] will display (when it should). If the
[Trees] should display after the IIF conditions, there is no data - only
0.0, even though the text boxes added have data. Could it be something with
the <> operator?
I really appreciate your help.

Thanks
/s/ Ray

Tempoarily add the Acress and Trees text boxes to the report.
Does it work now?
If so, just set the Visible property of the other 2 controls to No.
(Sometimes reports have trouble referring directly to a field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the promt reply. However, I have tried exactly that -
=IIf([CropCode]<244,[Trees],[Acres]) - copied and pasted from teh control
source, but for some reason the [Acres] field will work, but the[Trees]
will not. The spelling is correct including capitalization of "T". The
name of the text box was not the same. It was "AcreTree", and I changed
to "AcreTreeFld" just to make sure, but the IIf statement will not return
the value of the field [Trees}. It only displays 0.0. Seems kind of
crazy, but is probably something simple that I can not figure out.

Put an expression such as this into the Control Source of your text box:
=IIf([CropCode] < 244, [Trees], [Acres])

I am not clear about which way around you want those value, so you may
need to swap the Trees and Acres over.

Make sure the Name property of the text box is not the same as any of
the field names in your report, e.g. it cannot be called CropCode,
Trees, or Acres.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode],
which is either
244 value or <244. I cannot get the display field - which would either
be
the value of [Acres] or [Trees], to work properly. I have tried putting
a "text" value dependent on the [CropCode] field and works fine, but
cannot pick up the value of the fileds.

 

[Ray Thigpen]

Thanks for the prompt reply. I tried the method from Allen Browne first and
have not been able to make that work. I just tried this "query" method and
it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design view, add
a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box on
the report to that field. Note that a value of exactly 244 will produce
the result "Trees".

Another option would be to assign the values in the report's Print event
(txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in such
cases, so I'm not all that sure about the second choice I offered.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either

244 value or <244. I cannot get the display field - which would either
be

the value of [Acres] or [Trees], to work properly. I have tried putting a
"text" value dependent on the [CropCode] field and works fine, but cannot
pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[BruceM]

I'm not sure why it worked, since I seem to have misunderstood your original
question. Perhaps you substituted field names for the text in the query.

I never saw Allen's original reply in the thread. I didn't see it at all
until you responded to it. Don't know why.

Thanks for the prompt reply. I tried the method from Allen Browne first
and have not been able to make that work. I just tried this "query" method
and it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design view,
add a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box on
the report to that field. Note that a value of exactly 244 will produce
the result "Trees".

Another option would be to assign the values in the report's Print event
(txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in such
cases, so I'm not all that sure about the second choice I offered.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode], which
is either
244 value or <244. I cannot get the display field - which would either
be
the value of [Acres] or [Trees], to work properly. I have tried putting
a "text" value dependent on the [CropCode] field and works fine, but
cannot pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[Ray Thigpen]

Don't have a clue what the difference is, but at least it works. Thanks
again for both of your suggestions. The old timers used to say "2 heads are
better than 1".

Thanks
/s/ Ray

I'm not sure why it worked, since I seem to have misunderstood your
original question. Perhaps you substituted field names for the text in
the query.

I never saw Allen's original reply in the thread. I didn't see it at all
until you responded to it. Don't know why.

Thanks for the prompt reply. I tried the method from Allen Browne first
and have not been able to make that work. I just tried this "query"
method and it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design view,
add a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box on
the report to that field. Note that a value of exactly 244 will produce
the result "Trees".

Another option would be to assign the values in the report's Print event
(txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in such
cases, so I'm not all that sure about the second choice I offered.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode],
which is either
244 value or <244. I cannot get the display field - which would either
be
the value of [Acres] or [Trees], to work properly. I have tried putting
a "text" value dependent on the [CropCode] field and works fine, but
cannot pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[BruceM]

The difference is that the original code will cause either the word Acres or
the word Trees to appear in the text box. If you instead specify field
names (enclosed in brackets) then the contents of those fields should appear
in the query field. The way I can think of where these would be equivalent
is if the Acres field contains only the word Acres and the Trees field only
the word Trees in all records. If this is the case then the fields
themselves are probably unnecessary. The other possibility is that if you
leave out the quotes and the brackets in the expression then Access may
decide they are fields and put brackets around them automatically. It would
be interesting to know the actual expression that now appears in the query
field.

Don't have a clue what the difference is, but at least it works. Thanks
again for both of your suggestions. The old timers used to say "2 heads
are better than 1".

Thanks
/s/ Ray

I'm not sure why it worked, since I seem to have misunderstood your
original question. Perhaps you substituted field names for the text in
the query.

I never saw Allen's original reply in the thread. I didn't see it at all
until you responded to it. Don't know why.

Thanks for the prompt reply. I tried the method from Allen Browne first
and have not been able to make that work. I just tried this "query"
method and it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design view,
add a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box
on the report to that field. Note that a value of exactly 244 will
produce the result "Trees".

Another option would be to assign the values in the report's Print
event (txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in
such cases, so I'm not all that sure about the second choice I offered.

I have a report that opens in preview from a command button. I need the
text box to display 1 of 2 different fields, depending on the value of
another field within the report. The dependent field is [CropCode],
which is either
244 value or <244. I cannot get the display field - which would
either be
the value of [Acres] or [Trees], to work properly. I have tried
putting a "text" value dependent on the [CropCode] field and works
fine, but cannot pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[Ray Thigpen]

Bruce
The expression I used to make this work is:
AcresTrees: IIf([CropCode]>244,[Acres],[Trees])

Both of these fields contain numeric data. I use a similar expression in the
report itself to insert "text" dependent upon the value of the [CropCode]
field as well and it works fine. If the expression entered into the query
(which was used to gather data already) is entered into the Control Source,
the [Trees] field is not put into the report - ever. The [Acres] field can
be made to work either < or > and changing places with the [Field]. Not sure
what the difference is.

Thanks
/s/ Ray

The difference is that the original code will cause either the word Acres
or the word Trees to appear in the text box. If you instead specify field
names (enclosed in brackets) then the contents of those fields should
appear in the query field. The way I can think of where these would be
equivalent is if the Acres field contains only the word Acres and the
Trees field only the word Trees in all records. If this is the case then
the fields themselves are probably unnecessary. The other possibility is
that if you leave out the quotes and the brackets in the expression then
Access may decide they are fields and put brackets around them
automatically. It would be interesting to know the actual expression that
now appears in the query field.

Don't have a clue what the difference is, but at least it works. Thanks
again for both of your suggestions. The old timers used to say "2 heads
are better than 1".

Thanks
/s/ Ray

I'm not sure why it worked, since I seem to have misunderstood your
original question. Perhaps you substituted field names for the text in
the query.

I never saw Allen's original reply in the thread. I didn't see it at
all until you responded to it. Don't know why.

Thanks for the prompt reply. I tried the method from Allen Browne first
and have not been able to make that work. I just tried this "query"
method and it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design view,
add a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box
on the report to that field. Note that a value of exactly 244 will
produce the result "Trees".

Another option would be to assign the values in the report's Print
event (txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in
such cases, so I'm not all that sure about the second choice I
offered.

I have a report that opens in preview from a command button. I need
the text box to display 1 of 2 different fields, depending on the
value of another field within the report. The dependent field is
[CropCode], which is either
244 value or <244. I cannot get the display field - which would
either be
the value of [Acres] or [Trees], to work properly. I have tried
putting a "text" value dependent on the [CropCode] field and works
fine, but cannot pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray

 

[BruceM]

Glad to hear it worked. If the name of the text box bound to [Trees] is the
same as the field name (i.e. the text box is also named Trees, which is the
Access default name when you drag a field onto a report) you could have
problems with expressions.

Bruce
The expression I used to make this work is:
AcresTrees: IIf([CropCode]>244,[Acres],[Trees])

Both of these fields contain numeric data. I use a similar expression in
the report itself to insert "text" dependent upon the value of the
[CropCode] field as well and it works fine. If the expression entered into
the query (which was used to gather data already) is entered into the
Control Source, the [Trees] field is not put into the report - ever. The
[Acres] field can be made to work either < or > and changing places with
the [Field]. Not sure what the difference is.

Thanks
/s/ Ray

The difference is that the original code will cause either the word Acres
or the word Trees to appear in the text box. If you instead specify
field names (enclosed in brackets) then the contents of those fields
should appear in the query field. The way I can think of where these
would be equivalent is if the Acres field contains only the word Acres
and the Trees field only the word Trees in all records. If this is the
case then the fields themselves are probably unnecessary. The other
possibility is that if you leave out the quotes and the brackets in the
expression then Access may decide they are fields and put brackets around
them automatically. It would be interesting to know the actual
expression that now appears in the query field.

Don't have a clue what the difference is, but at least it works. Thanks
again for both of your suggestions. The old timers used to say "2 heads
are better than 1".

Thanks
/s/ Ray

I'm not sure why it worked, since I seem to have misunderstood your
original question. Perhaps you substituted field names for the text in
the query.

I never saw Allen's original reply in the thread. I didn't see it at
all until you responded to it. Don't know why.

Thanks for the prompt reply. I tried the method from Allen Browne
first and have not been able to make that work. I just tried this
"query" method and it works fine. I really appreciate your help.
Thanks
/s/ Ray

One way would be to base the report on a query. In query design
view, add a field:
AcresTrees: IIf([CropCode] > 244, "Acres","Trees")
This assumes that [CropCode] is a number field. Just bind a text box
on the report to that field. Note that a value of exactly 244 will
produce the result "Trees".

Another option would be to assign the values in the report's Print
event (txtAcresTrees is the name of the unbound text box):

If Me.CropCode > 244 Then
Me.txtAcresTrees = "Acres"
Else
Me.txtAcresTrees = "Trees"
End If

I think this would be in the Print event. I tend to use a query in
such cases, so I'm not all that sure about the second choice I
offered.

I have a report that opens in preview from a command button. I need
the text box to display 1 of 2 different fields, depending on the
value of another field within the report. The dependent field is
[CropCode], which is either
244 value or <244. I cannot get the display field - which would
either be
the value of [Acres] or [Trees], to work properly. I have tried
putting a "text" value dependent on the [CropCode] field and works
fine, but cannot pick up the value of the fileds.

Any help would be greatly appreciated.
/s/ Ray