Like and IS NULL in WHERE

[tcb]

This is my where clause:

WHERE qry_Preferred_ADD.A_City LIKE " & "'*" & Me.txt_City & "*'" & "
AND tbl_Person.P_PersonType_k='O' AND " & strWhereSector

From an unbound form a user can enter part of a text string in

txt_City like "saint" that will return "Saint Paul", "Saint Louis"
etc. If they enter a city, all rows that have a city are returned.
If they do not enter a city all rows with any city are returned.
That's okay.

The problem is when no city is entered in txt_City. I want it to
return all row with cities and all row that are null. But I don't
want it to return nulls when a city is entered in txt_City.

 

[Jeff Boyce]

One approach might be to do what I've inferred from your use of
strWhereSection. Can I safely assume that you are dynamically building the
string, based on some other choices? That's one way you could handle the
part of the WHERE clause that deals with City. If they leave City blank,
your WHERE clause could add a "OR IsNull()" test.

So, you are saying that if you leave out the city portion altogether, that
your query does NOT return rows with null City?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[tcb]

One approach might be to do what I've inferred from your use of
strWhereSection. Can I safely assume that you are dynamically building the
string, based on some other choices? That's one way you could handle the
part of the WHERE clause that deals with City. If they leave City blank,
your WHERE clause could add a "OR IsNull()" test.

So, you are saying that if you leave out the city portion altogether, that
your query does NOT return rows with null City?

--
Regards

Jeff Boyce
Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Yes, that is correct, sql is created dynamically based on many
choices. And the query does NOT return rows with null City when
selecting no city. The Or Is Null that I experimented with will
return rows with null city and rows with any text string. But it also
returns null cities along with "St. Louis" for example, which I don't
want.

 

[Jeff Boyce]

I misunderstood. I thought you wanted to see all rows, including nulls.

Are you saying you want to see ONLY null cities?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[tcb]

It's one of those things that if you could see it you would understand
in a second, but it's harder for me to explain in narrative especially
with my kids waking up and yelling for me! I hope this formats well
enough for you to read it okay.

When searching for a text string that contains "Ann" I get:

Annapolis City of Annapolis US local government
Annandale Manna Financial Planning Corporation US-based private
organization/individual
Ann Arbor Marker Actuarial Services, LLC US-based private
organization/individual
Annandale Private Industry Workforce Council 5 US-based private
organization/individual
Ann Arbor University of Michigan US Academic institution

that's good.

When not entering a text string I get:

Denver 3M US-based private organization/individual
St. Paul 3M US-based private organization/individual
Minneapolis 4th Judicial District US local government
Minneapolis Abbot Northwestern US local government
Cambridge Abt Associates, Inc. US-based private organization/
individual
St. Paul Access Philanthropy US-based nonprofit
Edina ACET Consulting, Inc. US-based private organization/individual
Eden Prairie Achieve Healthcare US-based private organization/
individual
Jamaica Plain Action for Boston US-based nonprofit


that's good but I want it also have the cities with null values

In my experiments with "or is null" I get this:

3M US-based private organization/individual
Denver 3M US-based private organization/individual
3M US-based private organization/individual
St. Paul 3M US-based private organization/individual
Minneapolis 4th Judicial District/Thomas Wexler US local government
Minneapolis Abbot Northwestern US local government
Abbot Northwestern US local government
Cambridge Abt Associates, Inc. US-based private organization/
individual

that is also good.

Herein is where the problem lies. With the "or is null" I also get
this when I find the text string that contains "Ann""

Annapolis City of Annapolis US local government
Annandale Manna Financial Planning Corporation US-based private
organization/individual
Annandale Private Industry Workforce Council 5 US-based private
organization/individual
Ann Arbor Marker Actuarial Services, LLC US-based private
organization/individual
Ann Arbor University of Michigan US Academic institution
3M US-based private organization/individual
3M US-based private organization/individual
8th Circuit Federal Court of Appeal US federal government
8th Circuit Federal Court of Appeal US federal government
Abbot Northwestern US local government

This is my problem. I do not want to include the nulls in this
instance.

 

[John W. Vinson]

It's one of those things that if you could see it you would understand
in a second, but it's harder for me to explain in narrative especially
with my kids waking up and yelling for me! I hope this formats well
enough for you to read it okay.

Try applying the criterion, not to the field itself, but to a calculated
field:

NZ([fieldname], "")

John W. Vinson [MVP]