Solved

Access Query based on 3 fields containing data

Posted on 2014-09-12
8
530 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
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 500 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

820 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