CODEWITHSUNDEEP
sqlvbams-access
Tyler
Tyler

Reputation: 123

Queries in access runtime return different result

I have a form with a few controls. The values entered into the controls are passed to the WHERE clause of a query I use to populate a list box on the same form.

Some of the columns that I am filtering on have null values. When the control is left empty, it should pull in all rows unfiltered by that column.

So part of my WHERE clause looks like this:

WHERE
(person.last_name like [Forms]![frmFilterPerson]![txtLastName] & "*"  
OR [Forms]![frmFilterPerson]![txtLastName] Is Null)

When I run my application in the full version of access, and I leave the txtLastName control blank, I get back ALL results, including the ones where the last name is null

However, when I run it in Access Runtime, I do not get all results, only the ones that have a value in the table for last name.

Any suggestions on how I can retrieve the rows that have null values while I have a filter in my where clause based on a control on my form?

Upvotes: 0

Views: 89

Answers (1)

Gustav
Gustav

Reputation: 55831

You can use this old trick - comparing the field with itself:

WHERE 
    person.last_name Like Nz([Forms]![frmFilterPerson]![txtLastName], person.last_name) & "*" 
    OR
    person.last_name Is Null

Or:

WHERE 
    person.last_name Like Nz([Forms]![frmFilterPerson]![txtLastName], person.last_name) & "*" 
    OR
    [Forms]![frmFilterPerson]![txtLastName] Is Null

Upvotes: 1

Related Questions