?
Solved

Access Query based on 3 fields containing data

Posted on 2014-09-12
8
Medium Priority
?
543 Views
Last Modified: 2014-09-12
I have a single table of data. i would like to be able to perform a query to find all the records that contain data in the fields.  Can be any data.  i am trying to filter out the records that have empty fields.  For example:  the fields first name, address, zip code.  So that I could create a query on the table that says show me all the records that do not have these fields blank.

Hope this makes some sense.  Looking for any assistance in getting this created.  Thank you in advance for any suggestions
0
Comment
Question by:FreddyBass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 2000 total points
ID: 40319983
This will give you all records where each of the 3 fields have data:

Select * from MyTable
Where trim("" & [first name]) <> "" AND trim("" & address) <> "" AND trim("" & [zip code]) <> "";

Open in new window


This will give you all records where at least one of the 3 fields has data:

Select * from MyTable
Where trim("" & [first name]) <> "" OR trim("" & address) <> "" OR trim("" & [zip code]) <> "";

Open in new window

0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40319990
This will give you records where all of the records have data. This looks for both empty strings and NULL values.

SELECT [first name], address, zip code
FROM <tableName>
WHERE ([first name] != "" or [first name] is not null)
AND  (address != "" or address is not null)
AND ([zip code] != "" or [zip code] is not null)
0
 

Author Comment

by:FreddyBass
ID: 40319993
Hi pdebaets,

Thank you for the reply...I am kind of rusty in Access 2010, would you be able to provide me with steps to accomplish what you refer to?  Do I go to Query Design and then simply write what you have posted after adding the table to the Query window?

Thank you in advance
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 12

Expert Comment

by:pdebaets
ID: 40320014
Yes, go to Query Design then from the ribbon select View > SQL View. Then enter the SQL statement above.
0
 

Author Comment

by:FreddyBass
ID: 40320028
This is the code I used:

Select * from master-contact-list
Where trim("" & [Notes]) <> "" OR trim("" & address) <> "" OR trim("" & [zip code]) <> "";

master-contact-list is the name of the table I am using.

When I click run it says I have a Syntax error
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 2000 total points
ID: 40320210
Try

Select * from [master-contact-list]
Where trim("" & [Notes]) <> "" OR trim("" & address) <> "" OR trim("" & [zip code]) <> "";
0
 

Author Comment

by:FreddyBass
ID: 40320325
Thank you pdebaets!!!

Worked like a charm...

Much appreciated
0
 

Author Closing Comment

by:FreddyBass
ID: 40320328
pdebaets gets the total of points, both his answers together provided the solution!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 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