Query wont show results if a Null value is in one of the fields

[jmcall10]

Hello everybody,

This is my first post on this site so be gentle

I have a query, that takes values from a form and then displays results according to the data entered. I will attach a screen dump of the query so you can see what I have in place.

The problem is, if any of the fields in the table have a null value then they do not get returned by the query. Now I have googled around and have found similar issues but with no resolve.

I hope someone here will be able to assist and tell me where I am going wrong.

Thanks in advance

jmcall10

 

[neileg]

Your Like statements will return ** if there's a null value. Take out one of the * and try again.

 

[namliam]

A null does never equal ANYTHING, that is with = <> Like or what ever....

Only way to get null's is to say "Or Is null"

Anytime you put any criteria on any column you are automaticaly say "AND IS NOT NULL", thus if you want NULLs you have to explicitely allow them.

 

[jmcall10]

Hi and thanks for responses

@neileg
Nothing changes by taking out one of either of the *. It still does not return the rows where there is a null value in any of the fields.

@namliam
I understand what you are saying and this seems to be a step forward, however if I put "or is null" in my query then it returns those rows with nulls all the time.

How would I change the query so that it if the field has a null then pretend it is something else. I.e. is the NZ() function not useful here?

I am not experienced enough to be able to put that into my query on my own unfortunetly.

Any ideas?

Regards,
Jmcall10

 

[MStef]

Try this, (in all columns).
Like .................. Or Is Null
But it is valid for "Number" Data type only.

 

[namliam]

That depends on what exactly your trying to do... NZ does replace a null field into some string, so perhaps that way is a way forward?

Alternative is ... you can do a lot of tricks with IIF and with playing/mixing up "and ... or ... or ... and" stuff in your query... Just a matter of beeing creative.

Managing NULL values requires some creativity and always requires your FULL attention. Or atleast untill such point that you are used to dealing with NULLs and their behaviour becomes second nature to you (trust me, this takes a while!)

 

[jmcall10]

@MStef
That would return rows where the search criteria = whatever the user enters and also the rows with nulls, therefore showing results that the user did not wish to see as they have not entered that data.

for example if I have 3 entries in a table and one of the fields is called "Notes", lets say one of those 3 entries has a null in the notes field. That record will always be returned by using "or is null" would it not?

@namliam
I think what you are saying is prob the best way forward, however I am not the best at creating queries the best of times. Is it possible for you to explain what I would have to do either using NZ() or IIF or mixing up and's or's etc?

If needed I guess I could strip down my database and post it here for someone to have a look at if they wanted?

I dont want to waste anyones time, but I appreciate all the help and assistance anyone can offer.

Regards,
jmcall10

 

[MStef]

I don't understand you. You said: "The problem is, if any of the fields in the table have a null value then they do not get returned by the query.".
It means you want to see records with Null value.

 

[namliam]

The problem is that if you search for Like "*" that excludes the Null values because that is basicaly searching for something...

So you want to check if anything is blank.

To get a better solution than this one though I suggest you have a search for "search form" in the references forum. This will show you how to do this " better " and circumvent this problem in the process.

 

[jmcall10]

Ok guys thanks for your input. I will have a search and see what it comes up with.

Thanks again

Jmcall10