?
Solved

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

Posted on 2009-02-10
9
Medium Priority
?
681 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?
0
Comment
Question by:DiannaHauf
  • 4
  • 3
  • 2
9 Comments
 
LVL 5

Expert Comment

by:JohnSingleton
ID: 23601100
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23601179
better to based your report on a query against the table.

select * from nameOftable
where [FieldName]<> Null
0
 

Author Comment

by:DiannaHauf
ID: 23602173
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Accepted Solution

by:
JohnSingleton earned 2000 total points
ID: 23602217
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23602283


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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23602292
use this query

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

Author Closing Comment

by:DiannaHauf
ID: 31545051
John, THANK YOU!  What does the nz stand for?  It works great!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23602411
:-(
0
 
LVL 5

Expert Comment

by:JohnSingleton
ID: 23602445

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question