IIF Statment to produce multiple values in a Query field

[DOHillier]

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

[Welshgasman]

Nothing is equal to Null I have seen mentioned.
Use IsNull() https://learn.microsoft.com/en-us/of...snull-function

[xps35]

Adding two extra fields is bad design. You need to create a new table for the related programs.

[orange]

D,
Can you show us the tables and relationships for the database on which you are creating reports? Adding extra fields does not seem appropriate for database (may work for spreadsheet).

[Gicu]

Agree with the others that you should have a join table to track the programs associated with each vessel).
To do what you're describing in the first post you need to use "+" to concatenate the slash and the two programs that might be missing; + and Null will return Null:
Program(s):[Program] & (" / " + [SecondaryPgm]) & (" / " + [TertiaryPgm])

Cheers,

[DOHillier]

First off, wow thanks for the quick replies,

Second I think I have it. The new function that appears to work is this:

IIf(IsNull([SecondaryPGM]) And IsNull([Tertiarypgm]),[program],IIf(IsNull([Tertiarypgm]), [program] & " / " & [secondarypgm], [program] & " / " & [secondarypgm] & " / " & [Tertiarypgm]))

FWIW I knew going in that it was bad design practice but my boss isn't really a database kind of guy. Not that I don't appreciate your advice however. I do have a table for programs for this very reason.

In response to Mr. Orange's question (and without going too much into it cuz I'm at work), the query gets its data from a table called [platformdaytoday]. The query pulls vessel/aircraft name [separate table], its location, programs assigned [yet another table], operational status, and commentary. The report pulls this data from the query where it's emailed out to stakeholders and senior management. This is how it's currently connected:





I apologize for posting too early and taking up your time. Next time I'll put in another hour or so just to avoid this happening again :/

Kindest Regards
Dale

[Gicu]

You should try my suggestion as well, looks a bit easier on the eye .

Cheers,

[Welshgasman]

[
I apologize for posting too early and taking up your time. Next time I'll put in another hour or so just to avoid this happening again :/]
I think 5 hours is enough TBH

You can also use Not IsNull ?

[DOHillier]

You should try my suggestion as well, looks a bit easier on the eye .

Cheers,

I'll give it a go and let you know how things go
D

[DOHillier]

Works perfect! Thanks again guys for your help. o/
D

[Gicu]

The same technique is usually used when assembling mailing addresses from various components that may or may not be populated.

Good luck with your project!

Cheers,