Multi-field query not working when some fields are null value

[Erik777]

Hello, I'm new here and relatively new to Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.

Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle.
I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.

The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"

It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.

I barely know what I'm doing and I'm surprised I actually got it working as well as I did. This one last little hiccup seems to be my only remaining problem.

I've attached the small database for review if anybody has any suggestions.

I would greatly appreciate any help. Thanks in advanced.

Erik

 

[rzw0wr]

Like[U] "*" &[/U] [forms]![Form1]![cboOffice] & "*"

Add "*" & to the front of your like statement.
Add to the top of your Job Title field in the query,

Title: IIf([Job Title]=Null,"",[Job Title])

Remove the like statement at the bottom of the Job Title field.

Dale

 

[Erik777]

Thanks for the reply, rzw0wr.

I'm confused about the 2nd part. Are you saying in Design View of the query at the top of the Job Title column (the Field row) I should add the code you suggested?
If so, I tried that (as well as removing the portion you said) and it errored out with "Undefined function 'If' in expression".

Thanks.

 

[rzw0wr]

Yes to all of your questions.
and not IF - IFF .



Dale

 

[Erik777]

Sorry...assumed that was a typo.
Tried Iff and got same error message "Undefined function Iff in expression...".

Edit: Okay...I saw your original email said "IIF" so I changed it to that and it works. Sorta. It works in that one scenario but it's broken other functionality...say if I wanted to see all the accountants regardless of office.
Choosing Accountant in my combobox, leaving the others blank returns all rows of the table.

I'll try to play around with your suggestions more and see if I can sort it out.
Thanks for the quick responses.

Edit2: Hmm...yeah. It's half-working. It's doing what I want given that one scenario but removing that "Like.." statement seems to have made it ignore my selection in the Job Title combobox on the form. If I select "Accountant" for Job Title and "Phoenix" for Office it returns all employees in the Phoenix office, ignoring my Accountant selection.

 

[rzw0wr]

Sorry. It is IIF.
Yes it does show all of the Job Titles.

Need to find a way to make the field filter by the Form1 combo box.

Hmmmm.

Not very good at SQL.

I will keep trying.

Dale

 

[Erik777]

Well I appreciate the help but don't go nuts trying to figure it out.
After more research, I believe I'm having the exact same problem as the person in this thread. Well, I guess I can't post threads but it's thread id 243518, if you can figure out how to access it another way.
It's showing up at the bottom of my thread in the "Similar Threads" section with a title of " Multi-field form - Null Values in Query".

Problem is I don't understand the suggestion given in the reply at all.
I mean I sort of understand what the code is doing, I just have no idea what to do with it or where to put it.

Also...apologies if I didn't explain my problem thoroughly enough.

 

[Galaxiom]

Title: IIf([Job Title]=Null,"",[Job Title])

Null cannot be compared with an equals. Null does not even equal Null.

Try this type of structure in your criteria cell:

Like [forms]![Form1]![cboOffice] & "*" Or Is Null

 

[Erik777]

Thanks Galaxiom.

I had tried that earlier but when I add the "Or Is Null" to the end of each of my 3 Like statements (because there could be null value in any of those 3 fields) if I search for something like all employees in one office it will return all records with the office value equal to the one I selected PLUS any record that has a null value in either of the other two fields.

I realize this probably poor database design but in actuality this little employee database was one I created specifically to explain my problem in a simplified way. The real database I'm working with has 2000+ records, was exported from Sharepoint and has around 15 of these fields that need to be searchable in this same manner, half of which have an occasional null value in them since the person who created the Sharepoint list made every field optional and not required.

I hope that all makes sense and doesn't create more confusion.

 

[Galaxiom]

It is about how the criteria are structured.

Things in the same row of the grid are treated as AND so all must be True to match. Then the rows are treated as OR so records that match any row will be returned.

With the criteria I posted you should get those which match at least one plus all those that are Null in all fields.

To avoid this add another column that removes the all Null records. There are a number of different structures.

This one covers both Nulls and Zero Length Strings.
Derive a field (using your field names):

Len([field1] & [field2] & [field3] & "")

Put > 0 in the criteria rows for that column.
(Untick the disply box because you don't need to see the results.)

 

[Erik777]

Thanks Galaxiom.

Where exactly does that bit of code go when creating the field?
Apologize for my ignorance.

 

[Galaxiom]

Type it in the cell where a field name usually goes in the query designer.

It will turn into
Expr1: Len([field1] & [field2] & [field3] & "")

Expr1 is a derived field. You can give it any name you want but it really doesn't matter.

 

[Brianwarnock]

May I make a few comments

Like "*" does not return NULLS, as you found
You should only use a Like construct if you want to use partial string searches as they must read every record , that is they cannot use indexes.
Where you are using combos you are obviously not doing partial searches and should therefore use
Field= forms!formname!comboname or forms!formname!comboname is null


Brian

 

[Galaxiom]

You should only use a Like construct if you want to use partial string searches as they must read every record , that is they cannot use indexes.
Where you are using combos you are obviously not doing partial searches

I think that a Like can use an index provided that the fixed characters are at the beginning.

Like whatever & "*"

A combo can have a value other than one in the RowSource if its LimitToList property is False.

 

[Erik777]

Appreciate the help guys.

Galaxiom - when I tried your suggestion of adding the extra column with the derived field, when I hit the button to run the query it now pops up a window asking me to enter a parameter for the First Name field. Always. I just hit enter (leaving it blank) and the results are the same as if I did not have the new derived field. I do not get the results that have null in any of the fields.

BrianWarnock - I tried your suggestion, changing all my criteria fields from what I had to:
= [forms]![Form1]![cboOffice] OR [forms]![Form1]![cboOffice] is Null
= [forms]![Form1]![cboJobTitle] OR [forms]![Form1]![cboOffice] is Null
= [forms]![Form1]![txtName] OR [forms]![Form1]![cboOffice] is Null

...and it appears to be working! The Name field search stopped working so I changed that one back to Like [forms]![Form1]![txtName] & "*".
And now it seems to be working exactly how I would expect.
I don't exactly understand why it's working though. I don't understand what that second part of the criteria is actually comparing against?? I would have thought maybe the criteria should look like this:
= [forms]![Form1]![cboOffice] OR is Null
= [forms]![Form1]![cboJobTitle] OR is Null
= [forms]![Form1]![txtName] OR is Null

If you wouldn't mind, can you explain what's going on there?

Also...it did something really weird to my query in Design View. See the attached screen capture.

Thanks and I really appreciate all the help.

 

[Brianwarnock]

Ok , firstly the design view, yep great it isn't , whoever I did this kind of thing I always did it in SQL view and saved the query from SQL view then it is left alone. It is so annoying that the GUI insists on changing something so simple into something impossible to understand and amend. I would have warned you if I had thought that you would change tour approach as you seemed to have soved the problem, also see below.

What the second part of the criteria is doing is saying that if the criteria Is Null select everything, thus the selection will now be dependent on the other criteria.
If as in your case the fields being tested can be null then when the query is run they will be selected, I understand that you do not want the record selected if all 3 are null.

Brian

 

[Brianwarnock]

Your code is not correct for lines two and three, it should be, SQL view

Where
(Office= [forms]![Form1]![cboOffice] OR [forms]![Form1]![cboOffice] is Null)
And (Jobtitle = [forms]![Form1]![cboJobTitle] OR [forms]![Form1]![cboJobTitle] is Null)
And (Lastname= [forms]![Form1]![txtName] OR [forms]![Form1]![txtName] is Null)

Not sure if I have the field names correct.

But as I said earlier if you leave all 3 null you will select all records including those where all 3 fields are null, which I feel is logical but would need to study the thread to see if that is what you want.

Brian

 

[Brianwarnock]

Galaxiom - when I tried your suggestion of adding the extra column with the derived field, when I hit the button to run the query it now pops up a window asking me to enter a parameter for the First Name field. Always. I just hit enter (leaving it blank) and the results are the same as if I did not have the new derived field. I do not get the results that have null in any of the fields.

This would suggest that there was a typo in the First Name field name in the query.

Brian

 

[Erik777]

Thanks for the explanation.
I *think* I understand it.
Either way, it's working as I would expect it and that's all that matters at this point. Now lets see if I can translate all that into my real database.
Thanks to each of you for taking the time to offer help!

Erik

 

[Brianwarnock]

We are all happy to help.
Come back if you have any problems.

Brian