Using "IsNull" with multiple data sources

[TonyB1983]

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]

Hi. Try

Forms!FormName.Box1 Or ...Box2 Or ...Box3 
Or (...Box1 Is Null And ...Box2 Is Null And ...Box3 Is Null)

 

[TonyB1983]

That seems to have worked! Thank you so much!

 

[theDBguy]

That seems to have worked! Thank you so much!

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.