Using "IsNull" with multiple data sources (1 Viewer)

TonyB1983

New member
Local time
Today, 16:04
Joined
Jun 4, 2019
Messages
6
I want query criteria to include data from multiple boxes on my form. If I use:

[forms]![frmfinrep]![nominalfinal1] Or [forms]![frmfinrep]![nominalfinal2] Or [forms]![frmfinrep]![nominalfinal3] Or [forms]![frmfinrep]![nominalfinal4] Or [forms]![frmfinrep]![nominalfinal5] Or [forms]![frmfinrep]![nominalfinal6]

Then it works, but only if one or more boxes are not null. If they're all null, then the report shows nothing. I need it to show everything. I can easily make it work perfectly for one box, but not multiple ones, using:

Iif(isnull([forms]![frmfinrep]![nominalfinal1]),[tbljobactualssnapshot].[anname],[forms]![frmfinrep]![nominalfinal1])

I need to adapt the statement directly above to include data from all boxes (nominalfinal1 through to nominalfinal6). I tried a hybrid of the two statements:

Iif(isnull([forms]![frmfinrep]![nominalfinal1]),[tbljobactualssnapshot].[anname],[forms]![frmfinrep]![nominalfinal1] Or [forms]![frmfinrep]![nominalfinal2] Or [forms]![frmfinrep]![nominalfinal3] Or [forms]![frmfinrep]![nominalfinal4] Or [forms]![frmfinrep]![nominalfinal5] Or [forms]![frmfinrep]![nominalfinal6])

But this doesn't work if more than one of the nominal boxes has data in (but otherwise fine). I get one of the warning messages saying it's too complicated to be evaluated.

Hope this makes sense!


Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:04
Joined
Oct 29, 2018
Messages
21,361
Hi. Try
Code:
Forms!FormName.Box1 Or ...Box2 Or ...Box3 
Or (...Box1 Is Null And ...Box2 Is Null And ...Box3 Is Null)
 

TonyB1983

New member
Local time
Today, 16:04
Joined
Jun 4, 2019
Messages
6
That seems to have worked! Thank you so much!
 

Users who are viewing this thread

Top Bottom