Solved

Access Query based on 3 fields containing data

Posted on 2014-09-12
8
528 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

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

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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