Problem with IIF statement and filtering a query

[DataJoe]

Hello there, I am very new to these forums and have a problem which I have been unable to resolve myself so would like some assistance to resolve if possible.

Background - I have a movie database and have created a form with numerous combobox and radio button controls in the header which are used to filter the records. These are all working very well. One of the combobox controls is called status and has values New, Premier, Selected and ???. I use these when choosing a film I might want to watch. I also leave the field blank when I don't require a value to be entered. I have used the following SQL to filter which works well:

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",[Forms]![frmMovies]![cbxStatusID])

This will show all records or the specific selected value. Now I will confess that I had to create a new column in the query as follows to allow for the null values which ensured all records were returned and the blank ones were not ignored:

Nz([Status],0)

The above column is where the criteria filter SQL is entered. Now to my problem. I would also like to filter out all the records that were null (or now have 0) just showing the records that have a value. So rather than just seeing New or Premier or Selected I want to see all of these but not the blank ones. My combobox for applying the filter is populated using the following:

SELECT DISTINCT Status.StatusID, Status.Status FROM Status UNION SELECT "0", "<ALL>" FROM Status UNION SELECT "0","<Not Null>" FROM Status ORDER BY Status.Status;

So I have also added <All> and <Not Null> to the list for selection. I have tried the following but cannot get the result I want:

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",IIf([Forms]![frmMovies]![cbxStatusID]="<Not Null>",<> '0',[Forms]![frmMovies]![cbxStatusID]))

If I just use the following, without the IIF statement, I get exactly what I want but without the individual or all availability:

<>'0'

I assumed the logic would be if <Not Null> was selected that the <>'0' would be used and filter out all the null valued records in the same way that selecting <ALL> filters for all records as the "*" is used.

I also tried these alternatives:

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",IIf([Forms]![frmMovies]![cbxStatusID]="<Not Null>",Not Like '0',[Forms]![frmMovies]![cbxStatusID]))

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",IIf([Forms]![frmMovies]![cbxStatusID]="<Not Null>",(Nz([Status],0)) Not Like '0',[Forms]![frmMovies]![cbxStatusID]))

But the Not Like does not work either.

Can anyone point me in the right direction as to what I should put instead of <>'0' as the True part of the IIf statement?

 

[plog]

I think you've chosen the wrong method to filter your form. You should be using Form.Filter:

Form.Filter property (Access)

Office VBA reference topic

learn.microsoft.com

When you use a query that looks at a form it gets super confusing to handle unused input and Nulls as you are finding out. Form.Filter accomplishes the same thing but its so much easier for a human to understand. Instead of jamming all your criteria into one area of the query (or often times multiple areas to account for Nulls/unused criteria), you build a string that only uses the criteria you want to apply.

So my advice is to give up on this method and use Form.Filter. Have your form's data be based on all the data from your query/table without criteria and then apply just the criteria you want using Form.Filter.

 

[DataJoe]

A big thankyou for plog and Pat for your responses above. My question wasnt worded very well and I wasnt able to get across what I was looking for which is my bad and for that I apologise. However, I have managed to find a solution to this and it was surprisingly a very simple one in the end. The problem was with the true part of the secondary IIf statement which was not working.

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",IIf([Forms]![frmMovies]![cbxStatusID]="<Not Null>",<>'0',[Forms]![frmMovies]![cbxStatusID]))

The <>'0' part was not being actioned as I expected. I tried putting just <>'0' without the IIf statement and it worked as I wanted but obviously didnt allow for different selections. When included within the IIf statement it didnt work which confused me. After a bit of trial and error I thought the 'Like' at the beginning was causing the issue and tried removing it but it just returned nothing. I needed the Like to be there. I eventually stumbled upon "*???*" instead of <>'0' when thinking of how the Like is used for filtering. This worked exactly as I wanted. The lowest character size I had was for the selection 'New' and all other selection choices were more than this and because 0 was single then this should be removed as I wanted. So using the wildcard before and after ??? I thought it should return all values equal to and greater than 3. So the corrected expression, which now works perfectly as required, is as follows:

Like IIf([Forms]![frmMovies]![cbxStatusID]="<ALL>","*",IIf([Forms]![frmMovies]![cbxStatusID]="<Not Null>","*???*",[Forms]![frmMovies]![cbxStatusID]))

I now have a combobox that allows me to filter for <All> (All records are shown), <Not Null> (All records that have a selection value are shown excluding null records) and the other 6 individual selections display only when selected.

Thankyou once again for your time.