Filter query that may contain null values

[johandenver]

Hello!

I have a problem that someone might know the answer for:

I have a database with 4 columns (Firstname, Middlename, Lastname and UniqueID)

I'm trying to make a query that filters out only certain people by typing names in textboxes. My problem is that Middlename and UniqueID may often be NULL and the query won't work then.
If I add "Or IS NULL" to the query I get too many records back.

Example (all rows below are different people):

Firstname Middlename Lastname UniqueID
Patrick Henry 1
Patrick Henry
Patrick R Henry
Patrick Henry 2

I'd like to be able use the textboxes below and only get "Patrick Henry 1"
Firstname Textbox: Patrick
Middlename Textbox:
Lastname Textbox: Henry
UniqueID Textbox: 1

 

[VilaRestal]

You test for null in the textboxes not the query.

Dim strSQL As String
strSQL = "SELECT * FROM Table1 WHERE "
strSQL = strSQL & "Firstname = """ & Me.Firstname & """"
If Nz(Me.Middlename,"") <> "" Then strSQL = strSQL & " AND Middlename = """ & Me.Middlename & """"
strSQL = strSQL & " AND Lastname = """ & Me.Lastname & """"
If Nz(Me.UniqueID,0) > 0 Then strSQL = strSQL & " AND UniqueID = " & Me.UniqueID

Replace Table1 with the name of the table.

And do with strSQL what you will: Create a query and open it, set it as the recordsource of a form, set it as the rowsource of a listbox or combobox, etc.

 

[johandenver]

Thanks. I have some problems getting your code to work though

My Table is called CS and all textboxes are called ...Box so I added those the code...I also realized that the column UniqueID is actually called "Unique Identifier" with a space in it. I guess that changes the code a little as well but I don't know where the change it.

Dim strSQL As String
strSQL = "SELECT * FROM CS WHERE "
strSQL = strSQL & "FirstnameBox = """ & Me.FirstnameBox & """"
If Nz(Me.MiddlenameBox,"") <> "" Then strSQL = strSQL & " AND MiddlenameBox = """ & Me.MiddlenameBox & """"
strSQL = strSQL & " AND LastnameBox = """ & Me.LastnameBox & """"
If Nz(Me.UniqueIDBox,0) > 0 Then strSQL = strSQL & " AND UniqueIDBox = " & Me.UniqueIDBox

I tried creating a new query with this but when I run it I just get a "Select Data source" popup window.

 

[VilaRestal]

OK if I understand it should be:

Dim strSQL As String
strSQL = "SELECT * FROM CS WHERE "
strSQL = strSQL & "Firstname = """ & Me.FirstnameBox & """"
If Nz(Me.MiddlenameBox,"") <> "" Then strSQL = strSQL & " AND Middlename = """ & Me.MiddlenameBox & """"
strSQL = strSQL & " AND Lastname = """ & Me.LastnameBox & """"
If Nz(Me.UniqueIDBox,0) > 0 Then strSQL = strSQL & " AND [Unique Identifier] = " & Me.UniqueIDBox

 

[VilaRestal]

To create a new query from it:

    Dim qdf As QueryDef
    Dim sQueryName As String
    sQueryName = "New Query"
    On Error Resume Next
    DoCmd.DeleteObject acQuery, sQueryName
    Set qdf = CurrentDb.CreateQueryDef(sQueryName, strSQL)
    DoCmd.OpenQuery sQueryName

Change "New Query" to whatever you want it to be. It will delete any existing query with that name and replace it.

 

[johandenver]

Thanks a lot

I still get a popup when creating and starting a query with it though.
See attachment. Am I missing something in my installation of Access?

 

[VilaRestal]

I assume that's not using the create query method I posted ^^
Try that, it should work.

 

[johandenver]

Yes, that was manually creating a query.

I don't understand though, I've only ever created queries manually or as a rowsource.
Should I put both pieces of code in an event code for example?

 

[VilaRestal]

Well it depends what you want. But all that code, in that sequence, could go in the click event of a button on the same form as the textboxes. That will create the query based on the contents of the textboxes and open it.

But there are other ways you could use the strSQL. If you create a form based on the query it creates you could open that form instead.

If however, you're form is already based on the CS table then you could set the form's recordsource to the SQL (and not bother creating a query from it):

Me.RecordSource = strSQL

 

[johandenver]

Yes, it seems to work
Thanks a million for helping me!

However I want to use the query to find all records containing the specified name and display another column called "Full Unitname" in a listbox.

Like this:
Full Unitname Firstname Middlename Lastname Unique Identifier
UnitAsia Patrick Henry 1
UnitEurope Patrick Henry
UnitSAmerica Patrick R Henry
UnitNAmerica Patrick Henry 2
UnitAustralia Patrick Henry 1

So that if I put Patrick Henry 1 in the different textboxes I would get UnitAsia and UnitAustralia in my listbox. Can I use strSQL to do this?

 

[VilaRestal]

Change the first line to:


strSQL = "SELECT [Full Unitname] FROM CS WHERE "

Then set the listbox rowsource to the strSQL (don't bother creating the query from it)

 

[johandenver]

Thanks again

I tried this as rowsource of my listbox but I got the attached error:

strSQL = "SELECT [Full Unitname] FROM CS WHERE "
strSQL = strSQL & "Firstname = """ & Me.FirstnameBox & """"
If Nz(Me.MiddlenameBox,"") <> "" Then strSQL = strSQL & " AND Middlename = """ & Me.MiddlenameBox & """"
strSQL = strSQL & " AND Lastname = """ & Me.LastnameBox & """"
If Nz(Me.UniqueIDBox,0) > 0 Then strSQL = strSQL & " AND [Unique Identifier] = " & Me.UniqueIDBox

 

[VilaRestal]

No don't set the rowsource to the code, set it to strSQL:

strSQL = "SELECT [Full Unitname] FROM CS WHERE "
strSQL = strSQL & "Firstname = """ & Me.FirstnameBox & """"
If Nz(Me.MiddlenameBox,"") <> "" Then strSQL = strSQL & " AND Middlename = """ & Me.MiddlenameBox & """"
strSQL = strSQL & " AND Lastname = """ & Me.LastnameBox & """"
If Nz(Me.UniqueIDBox,0) > 0 Then strSQL = strSQL & " AND [Unique Identifier] = " & Me.UniqueIDBox
[COLOR="Red"]ListBox1.RowSource = strSQL[/COLOR]

Change Listbox1 to the name of your listbox.

All that is VBA code that would go in an event procedure (the click event of a button perhaps, or the after update event of the textboxes)

 

[johandenver]

Ah, understood.

I tried putting the code in a click event after the textboxes are updated.
I got two errors that I've attached.

 

[VilaRestal]

Of course, that's with a blank UniqueID

Change the last line before setting the rowsource to:

If Nz(Me.UniqueIDBox,"") <> "" Then If IsNumeric(Me.UniqueIDBox) Then strSQL = strSQL & " AND [Unique Identifier] = " & CInt(Me.UniqueIDBox)

My bad, I hope that change makes sense: It's making sure UniqueIDBox contains a number and doesn't apply a filter for it if it isn't.

You might want to add input mask or validation to that box to stop users entering non-numbers there.

 

[johandenver]

Stupidly "Unique Identifier" wasn't a number but I've changed that now.
It works exactly as I want it to...well...almost

If I put a blank "Unique Identifier" then it picks up records that contain a "Unique identifier" as well.

So searching for Patrick Henry returns records that should only be visible for Patrick Henry 1. Hope I'm making sense

Thanks for all your efforts so far.

Oh and the values in the text boxes come for selections in other lists so it shouldn't be a problem with people entering the wrong kind of data.

 

[VilaRestal]

I see, in which case it's easier (no If Nz needed):

strSQL = strSQL & " AND Nz([Unique Identifier],"""") = """ & Nz(Me.UniqueIDBox,"") & """"

Will filter for those without Unique IDs if it is empty.

Perhaps you want the same modification for the middle name bit:

strSQL = strSQL & " AND Nz(Middlename,"""") = """ & Nz(Me.MiddlenameBox,"") & """"

(If MiddlenameBox is blank exclude records that have a middlename)

 

[johandenver]

PERFECT!
Thank you again for all your efforts. I'm so grateful for this!

 

[VilaRestal]

You're welcome

 

[johandenver]

By the way, can I make the listbox only contain unique values?

I've done this before by adding Totals and Group By in the query but I can't do that here can I?