We help IT Professionals succeed at work.

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

DiannaHauf
DiannaHauf asked
on
Medium Priority
722 Views
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?
Comment
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.


-John
CERTIFIED EXPERT
Top Expert 2016

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

select * from nameOftable
where [FieldName]<> Null

Author

Commented:
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.
-John

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

Ask the Experts
CERTIFIED EXPERT
Top Expert 2016

Commented:


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

Commented:
use this query

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

Author

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

Commented:
:-(

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])


-John
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.

OR

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.