condiitonal expression in report control

  • Thread starter Thread starter jmoore
  • Start date Start date
J

jmoore

Is it possible to write a conditional expression for 3 conditions? In a date
field, 01/01/7777 was used to signify not available, and 01/01/9999 for not
applicable.

Can I put an expression in the control source on a report that would give me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display "Not
Applicable", else display date.

I only recently found this site. It has been a great resource. Thanks to
everyone involved.
 
jmoore said:
Is it possible to write a conditional expression for 3 conditions? In a date
field, 01/01/7777 was used to signify not available, and 01/01/9999 for not
applicable.

Can I put an expression in the control source on a report that would give me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display "Not
Applicable", else display date.
Click to expand...


That's kind of ugly, but try using:

=Switch([field]=#1/01/7777#,"Not Available",
[field]=#1/01/9999#, "Not Applicable", True,[field])
 
Oops... I didn't notice that you wanted [Field] if neither condition is
met...

=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",[Field]))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Al Campagna said:
jmoore,
Try... (all on one line)
=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",""))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


jmoore said:
Is it possible to write a conditional expression for 3 conditions? In a
date
field, 01/01/7777 was used to signify not available, and 01/01/9999 for
not
applicable.

Can I put an expression in the control source on a report that would give
me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display "Not
Applicable", else display date.

I only recently found this site. It has been a great resource. Thanks
to
everyone involved.
Click to expand...
Click to expand...
 
Thanks so much. This works great. Now, is this same statement can set font
formatting, e.g., Not Available display in red?


Al Campagna said:
Oops... I didn't notice that you wanted [Field] if neither condition is
met...

=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",[Field]))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Al Campagna said:
jmoore,
Try... (all on one line)
=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",""))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


jmoore said:
Is it possible to write a conditional expression for 3 conditions? In a
date
field, 01/01/7777 was used to signify not available, and 01/01/9999 for
not
applicable.

Can I put an expression in the control source on a report that would give
me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display "Not
Applicable", else display date.

I only recently found this site. It has been a great resource. Thanks
to
everyone involved.
Click to expand...
Click to expand...
Click to expand...
 
jmoore,
I'd use Conditional Formatting for that.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jmoore said:
Thanks so much. This works great. Now, is this same statement can set
font
formatting, e.g., Not Available display in red?


Al Campagna said:
Oops... I didn't notice that you wanted [Field] if neither condition is
met...

=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",[Field]))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Al Campagna said:
jmoore,
Try... (all on one line)
=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",""))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Is it possible to write a conditional expression for 3 conditions? In
a
date
field, 01/01/7777 was used to signify not available, and 01/01/9999
for
not
applicable.

Can I put an expression in the control source on a report that would
give
me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display
"Not
Applicable", else display date.

I only recently found this site. It has been a great resource.
Thanks
to
everyone involved.
Click to expand...
Click to expand...
Click to expand...
 
Hi Al,

Thanks for your great suggestions. I have to export this report to Word
because others will other narrative sections will be added. The conditional
formatting does not carry over to Word and I was hoping there would be
another way that would.

Also, I just posted another question that I should have added to this
thread. I want to have the "Not Applicable" and "Not Available" wrap to two
lines, if that is possible.

Thanks!

Al Campagna said:
jmoore,
I'd use Conditional Formatting for that.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

jmoore said:
Thanks so much. This works great. Now, is this same statement can set
font
formatting, e.g., Not Available display in red?


Al Campagna said:
Oops... I didn't notice that you wanted [Field] if neither condition is
met...

=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",[Field]))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


jmoore,
Try... (all on one line)
=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#, "Not
Applicable",""))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Is it possible to write a conditional expression for 3 conditions? In
a
date
field, 01/01/7777 was used to signify not available, and 01/01/9999
for
not
applicable.

Can I put an expression in the control source on a report that would
give
me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display
"Not
Applicable", else display date.

I only recently found this site. It has been a great resource.
Thanks
to
everyone involved.
Click to expand...
Click to expand...
Click to expand...
 
jmoore,
Thanks for top posting... as long as we both use the same method, the
"flow" is easier to follow.

In the report section where the "Not Available appears, use the On
Format event to...

If [Field] = "Not Available" Then
txtField = "Not" & vbCrLf & "Available",
txtField.Forecolor = RGB(255,0,0)
Elseif [Field] = "Not Applicable"
txtField = "Not" & vbcrlf & "Applicable"
txtField.ForeColor = RGB(0,0,0)
Else
txtField.ForeColor = RGB(0,0,0)
End if

Either make the control 2 lines high, or use Can Grow.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


jmoore said:
Hi Al,

Thanks for your great suggestions. I have to export this report to Word
because others will other narrative sections will be added. The
conditional
formatting does not carry over to Word and I was hoping there would be
another way that would.

Also, I just posted another question that I should have added to this
thread. I want to have the "Not Applicable" and "Not Available" wrap to
two
lines, if that is possible.

Thanks!

Al Campagna said:
jmoore,
I'd use Conditional Formatting for that.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

jmoore said:
Thanks so much. This works great. Now, is this same statement can set
font
formatting, e.g., Not Available display in red?


:

Oops... I didn't notice that you wanted [Field] if neither condition
is
met...

=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#,
"Not
Applicable",[Field]))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


jmoore,
Try... (all on one line)
=IIf([Field]= #1/1/7777#, "Not Available",IIf([Field]= #1/1/9999#,
"Not
Applicable",""))
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Is it possible to write a conditional expression for 3 conditions?
In
a
date
field, 01/01/7777 was used to signify not available, and 01/01/9999
for
not
applicable.

Can I put an expression in the control source on a report that
would
give
me
the following results, and if so, how do I write it?

If [field]=01/01/7777 display "Not Available" OR =01/01/999 display
"Not
Applicable", else display date.

I only recently found this site. It has been a great resource.
Thanks
to
everyone involved.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top