Queries in access runtime return different result

Question

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?

I got trouble with a date criteria in runtime, solved it by using a parameter query or you can try WHERE (((Nz(person.last_name,"") Like [Forms]![frmFilterPerson]![txtLastName] & "*"))) — BitAccesser
– BitAccesser, Commented Jun 22, 2017 at 21:16
There must be something else going on, the Access runtime doesn't evaluate query criteria differently then the full version. — Andre
– Andre, Commented Jun 23, 2017 at 4:52
@Andre Of course it does (it happend only once to me), but I checked the SQL-Code with a MsgBox and it showed correct and worked fine copy and pasted into full-version (also with /runtime parameter). I will look it up and post it. — BitAccesser
– BitAccesser, Commented Jun 23, 2017 at 5:47

Gustav · Accepted Answer · 2017-06-23 16:20:05Z

1

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

edited Jun 23, 2017 at 16:20

answered Jun 23, 2017 at 8:21

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

BitAccesser Over a year ago

What is the advantage compared to Nz(person.last_name,"")? The still availible index of person.last_name?

BitAccesser Over a year ago

Doesn't work for my testdata (no Null values are shown). Is something missing?

Gustav Over a year ago

I didn't care for Null values. I've edited the answer to do that.

Gustav Over a year ago

@BitAccesser: I read it as if [Forms]![frmFilterPerson]![txtLastName] was Null all values should be returned.

BitAccesser Over a year ago

Looks good, if he always wants Nulldata returned, not sure if he only wants Nulldata when textbox is empty.

|

Collectives™ on Stack Overflow

Queries in access runtime return different result

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related