How to Use Nz in Microsoft Access to Handle Null and Empty Values

[@j.dasilva4567]

U just solve me a problem with a filter. Now i put a second time a column inside the nz function and therefore both null and blanks are all treated the same way, as i needed. Thank u.

[@seanmackenziedataengineering]

Awesome, nice work! 🛠

[@zqtan4092]

Thank you so much for this video. I have learned alot watching many of your videos.

[@seanmackenziedataengineering]

You’re welcome; thanks for watching!

[@nutritionistmudrashah]

Is there a way to eliminate a Null value column, like if I have 8 columns and I don't want the empty columns for that particular selection to show up, is there a way I can do that?

[@seanmackenziedataengineering]

For sure, you could test the columns; if you find all nulls then remove the column from your query by changing the columns while rebuilding it on the fly: kzbin.info/www/bejne/l4Suip2NhJiNgLM

[@satyabanukil779]

Sir hope you are fine. This time I have a problem which I cannot handle. Don't know whether it can be done in a ms access report. I have a filed and if in the form, if the value is zero, I don't want to display the field in the report. I tried with this code "if fieldname = 0 then fieldname.visible=False". But returning error.

[@seanmackenziedataengineering]

Great question. You most certainly can do this, by using the On Format event of the Detail section of your report. Open Design > click on the Detail bar to highlight it > if Properties not open already then right-click, Properties > Events tab > Click ... beside On Format line > Code Builder > OK In the Sub, you can put something like: Me!txtMyTextBox.Visible = iif(Me!txtMyTextBox = 0, False, True) Save it and go back and open your report. Give it a try!

[@satyabanukil779]

@@seanmackenziedataengineering Thnks for your prompt response Sir. Yesterday, while proceeding further with my project faced with another problem. Hope it doesn't make you disturbed. Initially I thought I would be handle it myself. But Access does not have built in function "RANK" which is very easy in Excel. In my project I have a field "SCORE" a number field. There is another field "RANK". Now I want that "rank" field would make the ranking based on the data in the field "SCORE". For example in the field "SCORE", in a row whose value is 155 and that is the highest value in that field. Accordingly, "RANK" would show 1 (being highest in rank) and so on. Is there any ready made function in Access as I don't know how to build a module. Thanks and Regards.

[@artistryartistry7239]

Great video!! Does NZ only work with field names or can it be used with variables?

[@seanmackenziedataengineering]

It also works with variables. Ex: if you load a variant type variable with a null, you can use Nz to give it a non null value, like a zero. Very handy!

[@artistryartistry7239]

@@seanmackenziedataengineering Ah thanks Sean, so it cannot be a string variable or something else, has to be a variant type?

[@seanmackenziedataengineering]

I think you CAN put a string in there. Generally when you don't know a value or what it will resolve to, the design pattern is to load a variant first and then if needed, use Nz to make sure it will resolve to a string, integer, etc. When you have a long procedure, it is much easier and cleaner to reference a variable than it is to use an expression over and over. For example. say varVal = Forms!frmSomeRidiculousLongName!fsubSomeCrazylongSubformName.Form("txtMySubformTextValueName") one time and referencing just varVal 15 times before finally converting it to a String using Nz rather than typing some long reference over and over again. Or, you may convert the variant sooner and reference strVal. So, Nz gives an easy way to convert that possible null without using CStr that could give an error. Very flexible.

[@gvgates2010]

Hi there Sea. Every time I try to run the query, i get this message "Undefined function 'Nz' in expression"... Can you please help me with this...cheers

[@seanmackenziedataengineering]

Can you post the expression you put in the query field? Let's take a look.

[@satyabanukil779]

Sir, I have a report with a currency field. I want to make it invisible where the value is zero. Is it possible? Kindly help.

[@seanmackenziedataengineering]

You can set the visibility of certain fields, but on Reports this is generally done using some of the report Events. In design view, try selecting the Detail bar (it will turn black) then go to properties. In the On Format property you can check the value of the control and set Visible = True or False. Click the ellipsis to open the code builder and it will make an event for you to put your code in.

[@satyabanukil779]

What shall be the code syntax. Visible property shall be false only field value is zero. You replied one year ago. Anyway it didn't work successfully

[@rincondeolocuilta]

Nice!

[@seanmackenziedataengineering]

Thx!

[@jungjungdelosreyes4147]

😟☹️ why its too many words and cursor its keep on moving, i feel dizzy. I like Richard ways of explaining… hope it will be the same too. Thanks

[@seanmackenziedataengineering]

Good feedback! I'll try to go more slowly. Thanks!