Query for counting

Hi Experts,
I have a query question.  I need to create a report, I have some records that haves Rejected and some records that have Returned, both are set as "Y" in the table also have the rejected date and returned date, user can select either Rejected or Returned but can't select both.  Now my question is how do I pull all the records that either Rejected or Returned?  Better I user the Date or "Y"?  Here is the field that I tried to pull the number for both but it's incorrect.


Who is Participating?
etech0Connect With a Mentor Commented:
In the criteria section under the RejectedDate field, put "Is Not Null"
Then in the next row of criteria, under the ReturnedDate field, put "Is Not Null"
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Not quite sure I follow, but:

1. I would use the date and get rid of the field with the "Y".    Both fields indicate the same thing; if something was returned or rejected.  But the date gives you more information besides the fact that something was returned or rejected.  It also gives you when it was.

2. You check I believe should be:

Rej/Ret: IIf(Not IsNull([PDREJDATE]  or Not IsNull([PDRETURNEDDATE], True, False)

Why don't you use design view, and put criteria in for the fields. Then you won't need to use iif.
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'.

urjudoAuthor Commented:
I tried JDettman's suggestion, but gave me error message.  I'm using the design view to do this query.
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted,
try Jims SQL like this:

Rej/Ret: IIf(Not IsNull([PDREJDATE])  or Not IsNull([PDRETURNEDDATE]), True, False)

If it still errors then please post the exact error message...
urjudoAuthor Commented:
thanks, this way works.
Glad to help!
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.

All Courses

From novice to tech pro — start learning today.