Trying to understand the purpose of <>Nz in a query for a form box. (1 Viewer)

[Wapug]

I asked this questio once before, almost 2 years ago to the day. I never got an answer that made much sense and Im back to it again.
On the product details form in the Northwind database there is a "Go to Product" box that has the coding below as its row source. I cant make out what the <>Nz function is doing here. Could someone please explain this?
SELECT [ID], [Product Code], [Product Name],[List Price] FROM Products WHERE [ID]<>Nz(Form![ID],0) ORDER BY [Product Code];

My main questions are what does the Where clause do here? Second, well I guess my second question depends on what the answer is to the first question.

 

[pbaldy]

Well, the Nz() function would replace a Null value in the form with 0 so the query didn't error. That said, the form reference doesn't look valid. It would normally be

Forms!FormName.ControlName

And the <> means "not equal to".

 

[theDBguy]

Hi. The way I read it, the query is returning all products if their ID does not match the one selected on the Form. If no ID was selected, the Nz() selects ID zero (0) as a default.

Edit: Oops, too slow...

 

[Wapug]

Thank you both. I think I see whats going on now. I guess I dont understand what would happen if the entire where clause were left out of that statement.

 

[theDBguy]

Thank you both. I think I see whats going on now. I guess I dont understand what would happen if the entire where clause were left out of that statement.

Without a WHERE clause, then the query will return all the records.

 

[isladogs]

The MS templates are designed to illustrate features available in Access
Unfortunately, many of the templates include poor coding which encourages bad practice.

I thought I'd look at the template in case I could see anything new.
I used the 2010 version of Northwind

I completely agree with the previous comments:
a) The purpose of the combo is to list all products other than the currently viewed product. So it is filtering out the ID value currently on view
b) Although it works for that purpose, the filter [ID]<>Nz(Form![ID],0) is non-standard. It should be: [ID]<>Nz(Forms![Product Details].[ID],0)
c) ID is the PK field in the table Products so it cannot be null. This means the Nz expression is normally superfluous. Instead use <>Forms![Product Details].[ID].
Or if you really must do so, you could use: <>Form!ID

So why is Nz used? Its purpose becomes clear if you click on the Save & New button so there is no record displayed. Now select the combo drop down. ID is then null and the combo lists ALL the available products.
If the Nz was excluded, the combo would now be empty

Hope that helps

I looked at some of the embedded macro code. Ugh! In my view, avoid - use VBA instead!

BTW I restored your previous version of this thread from 2018 as it contained an answer by MarkK that may be useful to others in the future