IIF IsNull or IsNotNull

[JohnLute]

I have a report text box with the following source:

=[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & " "
& [FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc]

How can I revise this so that the text box will display ONLY IF
[cbProfilesAssocsFacIDs] and/or [cbProfilesAssocsFacIDsLineIDs] has a value?
The text box is set to shrink - I just can't get the right code.

Thanks for your help!!!

 

[KARL DEWEY]

How can I revise this so that the text box will display ONLY IF
[cbProfilesAssocsFacIDs] and/or [cbProfilesAssocsFacIDsLineIDs] has a value?
Which? Must both have a value or either have a value to disply?

Here you must have both --
=IIF([cbProfilesAssocsFacIDs] Is Not Null AND
[cbProfilesAssocsFacIDsLineIDs] Is Not Null,
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & " " &
[FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc], Null)

Here you may have either --
=IIF([cbProfilesAssocsFacIDs] Is Not Null OR
[cbProfilesAssocsFacIDsLineIDs] Is Not Null,
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & " " &
[FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc], Null)

 

[JohnLute]

Thanks, Karl!

Which? Must both have a value or either have a value to disply?

Either. That's what I meant by and/or.

Here you may have either --
=IIF([cbProfilesAssocsFacIDs] Is Not Null OR
[cbProfilesAssocsFacIDsLineIDs] Is Not Null,
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & " " &
[FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc], Null)

This didn't occur to me and it seems simple enough however when I run it
dialog boxes prompt for values in [cbProfilesAssocsFacIDs] and
[cbProfilesAssocsFacIDsLineIDs]. If I click through the boxes and leave null
values then the report returns without the text box. If I enter values in the
boxes the report returns with those values.

Why the prompts and why are the [Forms] lines apparently being ignored...?

 

[JohnLute]

Whoops! I should've looked more closely before replying! Here's what I have
now:

="Facility and Line:
"+IIf([Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] Is
Not Null Or
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs]
Is Not
Null,[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & "
" & [FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc],Null)

This works fine however if [cb...FacIDs] has a value and [cb...LineIDs] is
Null (because to be Not Null requires a record to be in the underlying table)
then the text box displays like this:
"Facility and Line: 11 /"

It should display with the [FacilityName]:
"Facility and Line: 11 / MARZETTI"

Can you see the problem? It's beyond me.

 

[KARL DEWEY]

I can not see the whole query but do you have an inner join and just maybe
need a left join?

 

[JohnLute]

Now we're getting into scary territory! If you want to bail I won't feel bad!

I suspected a join problem, too and tried to find/resolve it but no luck.

Here's the query (followed by it's 2 underlying queries) that's behind the
report:

qryFGsProcessingProfilesAssociations:
SELECT qryFGsProcessingProfilesAssociations_Unfiltered.ProfilesAssociations,
qryFGsProcessingProfilesAssociations_Unfiltered.Description AS Description,
qryFGsProcessingProfilesAssociations_Unfiltered.Class AS Class,
qryFGsProcessingProfilesAssociations_Unfiltered.txtProfileID,
qryFGsProcessingProfilesAssociations_Unfiltered.txtFacilityID AS
txtFacilityID, qryFGsProcessingProfilesAssociations_Unfiltered.FacilityName,
qryFGsProcessingProfilesAssociations_Unfiltered.LineID AS LineID,
qryFGsProcessingProfilesAssociations_Unfiltered.LineDesc
FROM qryFGsProcessingProfilesAssociations_Unfiltered
WHERE
(((Switch([Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs]
Is
Null,True,[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs]
Is
Null,([txtFacilityID]=[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs])
Or ([txtFacilityID] Is Null),True,([txtFacilityID] Is Null) Or
(([txtFacilityID]=[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs])
And
([LineID]=[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs]))))=True));

qryPKProfilesAssocsFacIDsLineIDs:
SELECT tblPKProfilesAssocsFacIDs.ProfilesAssocsFacIDsID,
tblPKProfilesAssocsFacIDs.ProfilesAssociationsID,
tblPKProfilesAssocsFacIDs.txtFacilityID, tblFacilities.FacilityName,
tblPKProfilesAssocsFacIDsLineIDs.LineID, tblFacilitiesLineIDs.Description AS
LineDesc
FROM (tblFacilities INNER JOIN tblPKProfilesAssocsFacIDs ON
tblFacilities.txtFacilityID = tblPKProfilesAssocsFacIDs.txtFacilityID) INNER
JOIN (tblPKProfilesAssocsFacIDsLineIDs INNER JOIN tblFacilitiesLineIDs ON
tblPKProfilesAssocsFacIDsLineIDs.LineID = tblFacilitiesLineIDs.LineID) ON
(tblPKProfilesAssocsFacIDs.ProfilesAssocsFacIDsID =
tblPKProfilesAssocsFacIDsLineIDs.ProfilesAssocsFacIDsID) AND
(tblFacilities.txtFacilityID = tblFacilitiesLineIDs.txtFacilityID);

qryFGsProcessingProfilesAssociations_Unfiltered:
SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.ProfGroup, tblProfilesClassPK.Class,
tblProfiles.Activity, tblPKProfilesAssociations.txtProfileID,
qryPKProfilesAssocsFacIDsLineIDs.txtFacilityID,
qryPKProfilesAssocsFacIDsLineIDs.FacilityName,
qryPKProfilesAssocsFacIDsLineIDs.LineID,
qryPKProfilesAssocsFacIDsLineIDs.LineDesc
FROM (tblProfiles INNER JOIN tblProfilesClassPK ON tblProfiles.txtProfileID
= tblProfilesClassPK.txtProfileID) LEFT JOIN (tblPKProfilesAssociations LEFT
JOIN qryPKProfilesAssocsFacIDsLineIDs ON
tblPKProfilesAssociations.ProfilesAssociationsID =
qryPKProfilesAssocsFacIDsLineIDs.ProfilesAssociationsID) ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE (((tblProfiles.ProfGroup)=1 Or (tblProfiles.ProfGroup)=2 Or
(tblProfiles.ProfGroup)=3 Or (tblProfiles.ProfGroup)=4 Or
(tblProfiles.ProfGroup)=5 Or (tblProfiles.ProfGroup)=6) AND
((tblProfilesClassPK.Class)="PK") AND ((tblProfiles.Activity)="Active"));

--
www.Marzetti.com

I can not see the whole query but do you have an inner join and just maybe
need a left join?
--
KARL DEWEY
Build a little - Test a little

Whoops! I should've looked more closely before replying! Here's what I have
now:

="Facility and Line:
"+IIf([Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] Is
Not Null Or
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs]
Is Not
Null,[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDs] & "
" & [FacilityName] & " / " &
[Forms]![frmQueryFGProcessingFacIDsLineIDs]![cbProfilesAssocsFacIDsLineIDs] &
" " & [LineDesc],Null)

This works fine however if [cb...FacIDs] has a value and [cb...LineIDs] is
Null (because to be Not Null requires a record to be in the underlying table)
then the text box displays like this:
"Facility and Line: 11 /"

It should display with the [FacilityName]:
"Facility and Line: 11 / MARZETTI"

Can you see the problem? It's beyond me.