Iif statement on 2 fields

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

Guest

I have a report that contains 2 fields - [Pass / Fail] and [Mapping
Instruction]. I only want to show the Mapping Instruction field contains
"Fail". What is the statement I need to put in the Control Source for Mapping
Instruction?
 
VickiS said:
I have a report that contains 2 fields - [Pass / Fail] and [Mapping
Instruction]. I only want to show the Mapping Instruction field contains
"Fail". What is the statement I need to put in the Control Source for Mapping
Instruction?
Click to expand...

It is possible that a few words were lost from your question. Did you mean
"I only want the [Mapping Instruction] to be visible if the [Pass / Fail]
field contains "Fail"?

First, let me offer the suggestion that you can save yourself some possible
irritation if you do not use spaces in Field names. Assuming that the field
[Mapping Instruction] is displayed in Control txtMapInst and the field [Pass
/ Fail] is displayed in txtPassFail, you can put in the Print event of the
section:

If Me!txtPassFail = "Fail" Then
Me!txtMapInst.Visible = True
Else
Me!txtMapInst.Visible = False
End If

or, shorter but not as clear:

Me!txtMapInst.Visible = (Me!txtPassFail= "Fail"

The Control Source will let you set the value, but not the Visible property.
And, you can only use the "Me" notation in code.. you'd have to have a long
IIF with fully qualified references, so I wouldn't use the Control Source
for this purpose, although it _could_ be used.

Larry Linson
Microsoft Access MVP
 
Thanks for your help! It works perfect! I know that the names shouldn't
have spaces but it's a huge database I inherited with over 35 reports, almost
100 queries and over 30 forms. I just haven't had the time (or energy) to
change everything. Any hints on a quick way to do that?

Larry Linson said:
VickiS said:
I have a report that contains 2 fields - [Pass / Fail] and [Mapping
Instruction]. I only want to show the Mapping Instruction field contains
"Fail". What is the statement I need to put in the Control Source for Mapping
Instruction?
Click to expand...

It is possible that a few words were lost from your question. Did you mean
"I only want the [Mapping Instruction] to be visible if the [Pass / Fail]
field contains "Fail"?

First, let me offer the suggestion that you can save yourself some possible
irritation if you do not use spaces in Field names. Assuming that the field
[Mapping Instruction] is displayed in Control txtMapInst and the field [Pass
/ Fail] is displayed in txtPassFail, you can put in the Print event of the
section:

If Me!txtPassFail = "Fail" Then
Me!txtMapInst.Visible = True
Else
Me!txtMapInst.Visible = False
End If

or, shorter but not as clear:

Me!txtMapInst.Visible = (Me!txtPassFail= "Fail"

The Control Source will let you set the value, but not the Visible property.
And, you can only use the "Me" notation in code.. you'd have to have a long
IIF with fully qualified references, so I wouldn't use the Control Source
for this purpose, although it _could_ be used.

Larry Linson
Microsoft Access MVP
Click to expand...
 
VickiS said:
Thanks for your help! It works perfect!
Click to expand...

You are most welcome. I'm glad it does what you need.
I just haven't had the time (or energy) to
change everything. Any hints on a quick
way to do that?
Click to expand...

Two third-party software packages that get good reports (I have used both,
at times, successfully) are Speed Ferret (http://www.moshannon.com) and Find
& Replace (http://www.rickworld.com).

Just remember that, when making global changes, especially on a large
database, change a _copy_ to avoid disaster when the inevitable mistake
creeps in, and "careful" is the watchword.

My tip, however, wasn't intended to mean that you have to rush to change
existing databases -- it's just a good idea for future work. Details of the
most commonly-used naming convention for Access, the Reddick VBA Naming
Convention, can be found at Greg Reddick's site, http://www.xoc.net.

Larry Linson
Microsoft Access MVP
 
Back
Top