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

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?
DiannaHaufAsked:
Who is Participating?
 
JohnSingletonCommented:
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
0
 
JohnSingletonCommented:
set the filter in properties to trim(nz([nameField], "")) <> ""



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


-John
0
 
Rey Obrero (Capricorn1)Commented:
better to based your report on a query against the table.

select * from nameOftable
where [FieldName]<> Null
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DiannaHaufAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:


select *,[First Name]" & " " & [Last Name] as Expr1 from nameOftable
where [First Name]<> Null and [Last Name]<>null
0
 
Rey Obrero (Capricorn1)Commented:
use this query

select *,[First Name] & " " & [Last Name] as Expr1 from nameOftable
where [First Name]<> Null and [Last Name]<>null
0
 
DiannaHaufAuthor Commented:
John, THANK YOU!  What does the nz stand for?  It works great!
0
 
Rey Obrero (Capricorn1)Commented:
:-(
0
 
JohnSingletonCommented:

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.