Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

query criteria determined by IIf statement

Posted on 2007-11-15
4
Medium Priority
?
568 Views
Last Modified: 2010-04-21
Hello ~

I'd like to incorporate the following IIf statement into a query criteria:

IIf([forms]![frmMain]![ckConsent]=1,-1)

Implied IF the control value is NOT 1, display all records.
Alternatively, IF the control value is NOT 1, display records w/ -1 or 0 in the field.

Tried:
IF the control value is NOT 1, no records are displayed - as written
IIf([forms]![frmMain]![ckConsent]=1,-1, -1 Or 0)
Does not work.

I appreciate your ideas.
0
Comment
Question by:Chi Is Current
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 2000 total points
ID: 20296299
If I understand what you're trying to do the following in the critera I think will render the result you're after...

<> IIf([forms]![frmMain]![ckConsent],0, 1)

If chkConcent is true then all values with true will be displayed...
If chkConcent is false than all values true and false will be displayed because a boolean field will only contain -1 and  0 for values thus both are <> (not equal to) 1.  In the inverse condition we are saying not equal to 0 which would leave us with only values that are -1.

0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 20296365
Cee Wah!  That's exactly it.

Extra thanks for lightning fast response time and the mind reading. (I think I did not articulate that question very well.)

Interesting the way that works.

Thank you, Jacob
0
 
LVL 2

Author Closing Comment

by:Chi Is Current
ID: 31409500
Exceptional solution & explanation!!!!!!  Blindingly FAST!!!
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20296403
You're most welcome!!!

Rick
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

963 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