We help IT Professionals succeed at work.

MS Access - Report based on Table - How to exclude records

DiannaHauf asked
Medium Priority
Last Modified: 2012-05-06
I'd like to exclude records with blank data in the name field.  I'm showing the names by an expression so the first and last name display properly.  In the table this data is coming from, there are companies who do not have a persons name.  I would like to exclude these records from the report that was generated using the table.  I probably should have made the report based on a query but I'm green and did it the way I could.  Is there an easy way put the criteria if blank don't show?
Watch Question

set the filter in properties to trim(nz([nameField], "")) <> ""

this will capture all null, and those with any number of just blank spaces.

Top Expert 2016

better to based your report on a query against the table.

select * from nameOftable
where [FieldName]<> Null


I'm doing something wrong.  
In the Query
Company field criteria I have *
But I'm having a hard time with the name field which is Expr1: [First Name] & " " & [Last Name]
"where Expr 1:[First Name]" & " " & [Last Name] Is Not Null
Yet when I run the query it is still pulling records where there are no names in the name field.
I'm not sure what I'm doing wrong.
because you're sticking an empty space in your criteria expression in the concatenation. it then can never be a true statement

throw a trim around it and say <> ""

Trim(nz([First Name],"") & " " & nz([Last Name],"")) <> ""

that should do it.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2016


select *,[First Name]" & " " & [Last Name] as Expr1 from nameOftable
where [First Name]<> Null and [Last Name]<>null
Top Expert 2016

use this query

select *,[First Name] & " " & [Last Name] as Expr1 from nameOftable
where [First Name]<> Null and [Last Name]<>null


John, THANK YOU!  What does the nz stand for?  It works great!
Top Expert 2016


Nz will return a desired value if a null is found for another value.  if the value is not null, then it passes it straight through untouched.
so, in the above, we let everything not null through (including possible empty spaces), then trimmed everything down.
That would make anything null, or any number of blank spaces all = "", so we say, we want anything containing any value other than some number of blank spaces and/or combination of null values.

this is basically how you use nz, it'll return a string. if you wish to compare to a numerical value, throw a val() around it.

nz([Check value for Null], [Return value only if found to be Null])

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.