Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


If based on the outcome of 2 fields

Posted on 2011-04-21
Medium Priority
Last Modified: 2012-05-11
I am trying to create an access 2007 report. This is based on a cross tab query. I use a SQL statement of the basis for my crosstab and I want a value returned to be based on the combined vakue of two fields. the IF statement that I need to represent is something like the below.

IF field1 is one of 5 values and field2 = 0 then
IF field1 is one of 2 values and field2 = 1 then

What is the best way to do this?

Question by:JayceW
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 35440084
You can use this expression in the report or the query:
IIf([Field1] In ('a','b','c','d','e') And [Field2]=0,"
   IIf([Field1] In ('f','g') And [Field2]=1,

Open in new window

If field1 is a number use numbers for the values and don't use the single quotes.
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 35440090
try this as a column in the query

iif([f1] in(1,2,3,4,5) and [f2]=0,"orange",iif([f1] in (1,2) and [f2]=1,"brown","black"))

Author Closing Comment

ID: 35440318
Thanks for the speedy respose guys.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
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 Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…
Suggested Courses

571 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