condiitonal expression in report control

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.
 
M

Marshall Barton

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])
 
A

Al Campagna

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...
 
J

jmoore

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...
 
A

Al Campagna

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...
 
J

jmoore

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...
 
A

Al Campagna

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIf statement 2
calculations in report controls 4
Conditional Formatting 1
Expression / SQL Statement Help 9
field expression problem when using parameter 3
Grouping/Sorting Report 10
scrcpy, a tool to display and control Android devices from your desktop 6
Displaying a Date Prompt/Criteria in Report Header 2

Top