Solved

Access Query based on 3 fields containing data

Posted on 2014-09-12
8
520 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now