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

i'm your host sean mckenzie thank you for joining me once again on my channel on data analytics and data engineering in this episode we're going to go back to our microsoft access playlist and we're going to learn about how to handle null values or empty values in our queries and controls and in order to do that we're going to use the the nz function which is very very handy and if you do any amount of of access programming seriously you will need to know this function so without further ado let's get to our null and empty values in microsoft access okay so to get started i'm just using a access file that we've used for several other of our demonstrations has got a bunch of junk data in here but we're going to make a new table and we'll put an id at the top as an auto number and we'll put that as a key in a minute here and we'll add a field called my text and i'll set that to 50 length and then i'll uh i'll put my number as a number and then i'll do my date i guess as as a date and we can use a date field as well for this demonstration and then i'll go ahead and i'll put a primary key on there which is good practice and then i'll save our table so we've got our table called nz test and uh and then from there we can we can see we look at our data sheet view we've got an empty table and there's a text field and there's a number field and a date field that we can type into so i'll put abc in my text field i'll put a one in my number and then i'll just choose a date from our calendar here and for the for the date field and then and on the next field i'll put def and then i'll leave my number as a null and uh i'll leave the date as a null as well null meaning empty or or unknown and then i'll leave you know text null entry on the third row and i'll put some date into my date on the third row so we've got some some null values in there and we can proceed and take a look so i'll go on to the create ribbon and i'll create a query design and then i can you can just close this pop-up table that has all our junk data from our previous examples in there and i'll you can grab the nz test table and just drag it onto your query and then we can go ahead and take a look at uh what we're gonna get in our output so the first thing we'll do is uh using this table here that we created we'll just double click and add our our three columns that mean something to us that'll be the my tax my number and my date fields okay and then if we look at that in datasheet view you can see our empty values are still there and so we can save this before we proceed and make any changes to it we'll call it our nz query and hit ok and that's going to save our query for us so what we're going to do in order to change our data is we're going to use the same field that we selected but we're going to change the name and we're going to we're going to add the nz function to it so we'll call this first one full underscore text and then the colon meaning that we're naming our field and then we'll we'll put our nz function in there around my text and then we'll put a comma after my text and we'll put none which means we want to show the word none if my text is is null you'll notice that it put the square brackets around there automatically for me around the my text field so we can do the same thing for full number where we'll say full number colon and then we'll put the nz uh my number and then comma and then we'll put a zero since this is a numeric field so that means we want to show an a zero if there's an a null field or an empty field and then for the last one we'll do the same thing for a date we'll say full date as our field name and then we'll we'll put the colon and then nsaid and then my date and then we'll put a date literal in in there for our expression meaning we'll use the hashtags and we'll just put one one 2020 as you know the default date if nothing is in that field and so so that shows you uh text number and date and so we can save that and then we'll we'll look at the the data sheet view and as you can see it put the word none in there put a zero where the other null was and it put the 20 20 0 1 0 1 where where the date was empty so everything's all hunky-dory right well there is one big gotcha and it has to do with the text fields and the fact that you can put you can put an empty string into a text field so to open the immediate window use your control g as i showed you in previous episodes and in the immediate window you can type in question mark and then nz and then put null and then one and you'll see that that gives you back a one because the what was passed into it was a null if we did nz abc and def you'd get a b c because there's obviously a value and it's not null that's being passed into nz but what if we passed in quote which is a zero length string and we put none you'll notice that it passed back a zero length string because a zero length string is not null and null means unknown but if we have a zero length string then that can be inserted though it's it's not very common in access because normally when you type in fields and you do back up or erase a value in a field access will put a null in there for you but in some cases where there are actually zero length strings in some data or or maybe some code you know in an application that you wrote so i'm going to simulate that now it might put in a value into your table that has a zero length string and then when you use your nz function on a string or on a text field it'll still show that it's empty so if i put in insert into nz test you know an empty string into there it says uh you're about to append a row um then we've appended a row we put in we've inserted a row with with an empty string in there or a zero length string so now if we open our query that we put our nz functions on you'll see that we still have a field that looks empty even though even though we wanted to to have the empty values or null values replaced and so as you can see it looks like it's a null what's in num row 3 is a null in the text but row 4 is an empty string or a zero length string and so this is one thing that is a big gotcha and it can really screw you up so if you know about it in advance you can sort of plan for it if you know that there's a possibility you might have some zero length strings so i just expanded this this field here and as you can see we've got our nz in there like we typed in in the first place and we're just going to change that and we're going to use an iif statement saying if this then that in one expression and so we're going to do a concatenation we're going to say empty string and then the ampersand and then my text field we'll put a len which is the length function around it and we'll say if the length length of my text is equal to zero then put in none um otherwise put in my text and you it's good to do the concatenation because it'll resolve the null into an empty string and it'll work for nulls or empty strings and so that's the way you can do that and that's how you can do nulls and empty strings using nsaid and the length function in microsoft access hope you enjoyed today's discussion on how to handle null and empty values and access if you like what you saw please give the video a thumbs up and make sure to subscribe to the channel if you have not subscribed yet and click the bell when you see the bell so that you'll be notified of any new content that i put up if you have any questions or comments about what you saw today please go ahead and put those in the comments section below and i'll do my best to answer your questions uh as well as i can have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 2,146

Keywords: nz handle null and empty values in ms access, access null values, nz function, access empty string, access len function, ms access nz, ms access query, ms access concatenate, how to handle empty value in ms access, how to handle null value in ms access, data analytics, data engineering, sean mackenzie, nz hadnle null adn empty values in ms access

Id: CR3FmVoRFys

Length: 9min 56sec (596 seconds)

Published: Tue Feb 23 2021