Not IN not able to take null values

[Triacona]

Dear All,

Great forum!

I have a the following senario.

Query - EnAllocationOverdue

Form - MainScreen

Within the query the field ENSTAT.UNI7LIVE_ENCASE is the one I wish to filter using the form.

In the form the fields AddSrch and SrchCrit are the fields the user fills in and this then should exclude the data from the query.

I have tried serveral variations on the field within the query.
It seems to work only when there are values within the form fields.

Here are some of the examples I have tried in the ENSTAT.UNI7LIVE_ENCASE query field.

1

Not In ([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit])

2

Not In (Nz([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit]))

3

Nz(Not In([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit]))

4

Not In (Nz([Forms]![MainScreen].[AddSrch]),Nz([Forms]![MainScreen].[SrchCrit]))

None of the above give results when the fields are blank, only when they are filled in does it give results, i.e. the exclusion of the values within the form fields.

Any help would be greatly appreciated

Thank you


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have figured it out!

The solution is:

Not In (Nz([Forms]![MainScreen].[AddSrch],"ZZZZZ"),Nz([Forms]![MainScreen].[SrchCrit],"ZZZZZ"))

Thanks

Thank you,

Kind regards

Triacona