IIF - UGH!

[Kim W. via AccessMonster.com]

Info is pulled from an oracle database and manipulated in Access through
various queries. Data pulled is for higher education purposes/tracking.

THE BACKGROUND-

Fields Utilized in this Report are:

Couse_ID: College Course Name (BIOL M12)

Equiv_End_Term1: If an equivalency for a course has ended, the semester and
year of the course ended are entered (200305 which equates to Summer of 2003).
If there isn't an end date to the equivalency, then a number is given to
represent that (999999). There can be up to 11 equivalencies and some may be
blank (no data).

Equiv1: Course Name of the Equivalency (BIOL M02)

THE PROBLEM-

I want Equiv1 to show if the Equiv_End_Term1 equals 999999 otherwise it
should show "EDC" (Equivalency Discontinued) - HOWEVER, if the field is blank
(has no data), it should remain blank.

This is what I have so far and it works for the first part...however, it
returns all of the blank fields with EDC too!

=iif([equiv_end_term1]=999999,[equiv1], "EDC")

How would I get the second part of my equation to work?
I've played with =iif([equiv_end_term1]=999999,[equiv1], "EDC") or iif(
[equiv_end_term1] is null, "")

But of course, it doesn't work. HELP!!

Thanks so much for your help!

 

[John Spencer MVP]

=iif([equiv_end_term1]=999999 or [equiv_end_term1] is null,[equiv1], "EDC")

or another option

=iif(Nz([equiv_end_term1],999999)=999999,[equiv1], "EDC")



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[duckie1126 via AccessMonster.com]

YEAH!!!!! That worked. Of course!

Thanks so much!

_________________________________________________________

=iif([equiv_end_term1]=999999 or [equiv_end_term1] is null,[equiv1], "EDC")

or another option

=iif(Nz([equiv_end_term1],999999)=999999,[equiv1], "EDC")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Info is pulled from an oracle database and manipulated in Access through
various queries. Data pulled is for higher education purposes/tracking.

[quoted text clipped - 31 lines]

Thanks so much for your help!