Problem with IIF statement and filtering a query (1 Viewer)

[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.

 

[Pat Hartman]

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

This expression forces a full table scan. It prevents the database engine from using an index on StatusID assuming there is one.

Where StatusID = Forms!frmMovies!cbxStatusID Or Forms!frmMovies!cbxStatusID = "<All>" Or Forms!frmMovies!cbxStatusID Is Null

 

[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.

 

[Pat Hartman]

I guess you didn't understand the point. Do not use LIKE when you have a complete value. When you pick something from a combo, you have the full value, NOT a partial value. You only want to use like if you are working with a string and you want to find "close" values. So if you have a street name but not a complete address, you would use like.

Where Addr1 LIKE "*Main*"

But when you pick from a combo, you ALWAYS get a full value whether it is a string or a number. Therefore, you never use LIKE and you always use =.

Where StatusID = Forms!frmMovies!cbxStatusID Or Forms!frmMovies!cbxStatusID = "<All>" Or Forms!frmMovies!cbxStatusID Is Null

Look again at this expression. It compares the combo to to the status. That handles the case where a value is selected from the combo. The Or Forms!frmMovies!cbxStatusID = "<All>" handles the case where the user chose the <ALL> value from the combo. The final OR handles the case where no option was chosen from the combo. I never bother with "All" unless the user is not capable of understanding that no selection means All. So the Is Null would handle the no selection option.