Access 2010 Query

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-13
i have a table that i want a query to give me record number of each recard that have on of 5 field as empty or 0 rigth now i did this and get only the one that all are empty

Field1         field2        field3        field4      field4
null or 0      null or 0     null or 0   null or 0  null or 0

but if let say record 3 has field3 with 3 in it and the other field null it dont so up in the report
Question by:ntsabman
LVL 54

Accepted Solution

Rgonzo1971 earned 1000 total points
ID: 39483318

Is this what you want?

LVL 61

Assisted Solution

mbizup earned 1000 total points
ID: 39483349
This will give you rows where *exactly* one of the five fields is null or 0:

SELECT Table1.N°, Table1.Field1, Table1.field2, Table1.field3, Table1.field4, Table1.field5
FROM Table1
Where ABS((NZ(Field1, 0 ) = 0) + (NZ(Field2, 0 ) = 0) + (NZ(Field3, 0 ) = 0) + (NZ(Field4, 0 ) = 0) + (NZ(Field5, 0 ) = 0)) = 1

Open in new window


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

587 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