Good morning,
At my work we utilize a database that generates a series of reports on a daily basis. One of these reports lists the various projects/programs that our vessels are assigned to under the field name [Program]. Recently my boss, in an effort to track multi-tasked vessels, has requested that I add two extra fields, which I have called [SecondaryPgm] and [TertiaryPgm]. Due to the lack of available space on the sheet I am forced to look at combining these three fields into one.
In the query that the report is based on, I created a new field and used the following IIF statment:
IIf([secondarypgm]=Null,[program],IIf([TertiaryPgm]=Null,([program] & " / " & [SecondaryPgm]),([program] & " / " & [secondarypgm] & " / " & [Tertiarypgm])))
The idea here is that if the secondary and tertiary program fields are not present (i.e. NULL) then it would only show the Primary program field. If there was a secondary program [but not a tertiary] it would show: [Program] & " / " & [SecondaryPgm] on the query.
Finally if there was a tertiary program, then the instruction on the query would be: [program] & " / " & [secondarypgm] & " / " & [Tertiarypgm]
My issue here is that the IIF statement I'm using seems to default to the 'False' result everytime which winds up looking like this: Program //
Obviously this isn't really what I want to see. I've tried starting small, using the NZ function, and so far have come away fruitless. I have a strong feeling that it's probably a syntax thing but 5 hours at it is enough for now. Any idea what I'm doing wrong?
Kindest Regards
D