IIF / OR - Multiple conditions based on multiple fields

I want the OnPrint of a report to check three fields and run code if any ONE 
of the conditions is met:

Iif ([MyTextOne]=1 OR [MyTextTwo]=2 OR [MyTextThree]=3) Then
RunSomeCode
Else
RunOtherCode

I thought of using Select Case and calling procedures as each condition is 
met:

Select Case Me![MyTextOne]
Case is = 1
Call Check2
Case else
Stop
End Select

Check2 ()
Select Case Me![MyTextTwo]
Case is = 2
Call Check3
Case else
Stop
End select

Check3 ()
and so on.........


It will work, but gets a bit too long as the test conditions increase. It 
gets just as long with nested select case or nested IIf.
0
Utf
10/14/2007 7:36:02 AM
access 16762 articles. 2 followers. Follow

2 Replies
1004 Views

Similar Articles

[PageSpeed] 15

The first line of code (with the OR conditions) is probably the best if you 
only have 2 lots of code to choose between.

If you have lots of OR conditions, there's a very good chance that the 
problem is with the data structure. Instead of lots of repeating fields, you 
might want to consider whether it would be better to use a related table 
with a *record* for each, in place of the many fields for the options.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Ventouris" <TomVentouris@discussions.microsoft.com> wrote in message
news:DAEA70F6-C170-48ED-961B-7A86F50E37CD@microsoft.com...
>I want the OnPrint of a report to check three fields and run code if any 
>ONE
> of the conditions is met:
>
> Iif ([MyTextOne]=1 OR [MyTextTwo]=2 OR [MyTextThree]=3) Then
> RunSomeCode
> Else
> RunOtherCode
>
> I thought of using Select Case and calling procedures as each condition is
> met:
>
> Select Case Me![MyTextOne]
> Case is = 1
> Call Check2
> Case else
> Stop
> End Select
>
> Check2 ()
> Select Case Me![MyTextTwo]
> Case is = 2
> Call Check3
> Case else
> Stop
> End select
>
> Check3 ()
> and so on.........
>
>
> It will work, but gets a bit too long as the test conditions increase. It
> gets just as long with nested select case or nested IIf. 

0
Allen
10/14/2007 8:44:36 AM
Thanks for the response.

I thnk the scenario might add some value to my question. 

The data on-screen is sent to to an external file as soon as three 
conditions are met. The export is done with a command button. The conditions 
to be met are specific values in three fields. I am trying to prevent the 
users from exporting data which does not meet the required conditions and 
alert them with a message that they should check the three fields.

Alternatively, I will use the same process to discable the command button 
until the three conditions are met.

A clumsy work around, but here's what I have done in the meantime:

An unbound MyTextBoxCheck on the form with control source 
[MyTextOne]&[MyTextTwo]&[MyTextThree]. An IIf statement checks for an exact 
match in the MyTextBoxCheck and runs appropriate code.

Those of us who don't know must work a little harder! :)

Thanks to your site and others like yours, it's been a while since I've had 
serious problems as a result of data structure.



"Tom Ventouris" wrote:

> I want the OnPrint of a report to check three fields and run code if any ONE 
> of the conditions is met:
> 
> Iif ([MyTextOne]=1 OR [MyTextTwo]=2 OR [MyTextThree]=3) Then
> RunSomeCode
> Else
> RunOtherCode
> 
> I thought of using Select Case and calling procedures as each condition is 
> met:
> 
> Select Case Me![MyTextOne]
> Case is = 1
> Call Check2
> Case else
> Stop
> End Select
> 
> Check2 ()
> Select Case Me![MyTextTwo]
> Case is = 2
> Call Check3
> Case else
> Stop
> End select
> 
> Check3 ()
> and so on.........
> 
> 
> It will work, but gets a bit too long as the test conditions increase. It 
> gets just as long with nested select case or nested IIf.
0
Utf
10/14/2007 9:06:01 AM
Reply:

Similar Artilces:

How do I calculate a value based on dates?
I have a worksheet that calculates YTD (year-to-date) totals from 3 categories...labor, travel, and other expenses. I want to add a row that will separate monies spent from a range of dates (i.e. 1st of the month to the last Friday of the month) in order to calculate monies spent MTD (month-to-date). I am willing to change the monthly values if need be. Hi, John- I arrived at a solution for this by mocking up some data: ~Column headers "Date" "Type" and "Amount" in A3:C3 ~Date entries from Feb to May in A4:A15 ~Line identifiers "Labor" "Trave...

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

Conditional Formats
Hi I have a row of dates, one week apart and I use the conditional format to highlight the current week. I would like to highlight the column 12 cells below the current date which contain text . I tried numerous things ,but my knowledge is limited. Is it possible to do? Thankyou. Apply a conditional format to that cell, the one 12 below Refer to the original set of dates. Steve On Thu, 25 Jan 2007 05:59:40 -0000, ned <ned@iinet.net.au> wrote: > Hi > I have a row of dates, one week apart and I use the conditional format= to > highlight the current week. I would like ...

IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...

Position Extra Fields Screen Error
"This employee record has been changed since you opened this window. Changes won't be saved." My HR users are seeing an error when trying to update a field in GP 10.00.1502 The window is the "Position Extra Fields" screen, accessed from the "Position Setup" screen. If they update any data on the "Position Extra Fields" screen and click OK, they receive the above error. I tried and even as admin, I see the same error. A SQL trave shows it trying to insert the data to HRPOS022, then the next SQL access is to DYNAMICS.dbo.SY01700, wh...

From Field #4
Hi When I am using the the from field not able to send email Please, help me to solve the problem Thanks Post more info like version of Outlook, type of mail account (Exchange, POP3, IMAP,...) etc... -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Siva" <sivanath@singnet.com.sg> wrote in message news:CED6DEAD-BCC7-40F9-AE13-C024FEE13588@microsoft.com... > Hi, >...

Condition on sales stage
hi I want to insert a condition on the top of the sales stage like this : if <PicklistName>=a then Stage 1 ... Stage 2 ... ... else exit Depending on the value of the picklist present in the opportunity form, this opportunity will have or will have not a sales process. It is possible? Thanks Chris, Another way top solve this is to put together a workflow rule that executes on the creation of an opportunity and based on the value of the picklist would execute a different sales process. "Chris" wrote: > hi > > I want to insert a condition on the t...

iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...

CDO 1.21-based program?
What are some examples of a CDO 1.21-based program as mentioned in KB892560? We are seeing the behavior mentioned in the KB and I am working on recreating the problem. TIA Do you have a program automatically using meeting requests? I would think that if your organization has such programs you should know about them. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "SilverICE" <SilverICE@discussions.microsoft.com> wrote in message news:8EECCB0A-2320-4A89-82A4-E3DA52B083D1@microsoft.com... > What are some examples of a CDO 1....

Translation of fields
Hi. I have a CRM 4.0 with English as system language, and Danish as a second language. When i create a new entity or add new fields, I can't find the place to type in the translation of the created fields. Can this only be done by export all the translations, edit in e.g. Excel and then import again or is there an better way when it's only a few new fields that needs translation. Regards Jacob Mondrup Hi, No, it is the only way to do it. -- http://www.nauta-automatisering.nl "Jacob Mondrup - Vivant" wrote: > Hi. > > I have a CRM 4.0 with English as...

conditional email macro
I'm in way over my head and hoping for help. Our office is in an exchange environment and we use a large excel book to schedule everyone (stored in a management folder with limited access). We autopublish each sheet as a month in a shared folder so all staff can access it but not see the entire master excel book. Each date is in Row2 (columns B-V depending on the month) with staff names in the following rows of column A. Each month is a seperate sheet. My problem is changes within 2-3weeks of the date and people not noticing changes. I'd like to create a macro that applies...

Conditional sum
I am looking for a formula that will add cells based on condition. Ex. If i have ten cells with different values (130, 131, 143, 142, 144,130, 132,141,130.50,141.70) The condition is, if any cell is greater than 141, it should take only 141. Any help will be appreciated. Thanks. =SUMIF(A:A,">141") This sums everything in column A greater than 141. HTH Jason Atlanta, GA >-----Original Message----- >I am looking for a formula that will add cells based on >condition. Ex. If i have ten cells with different values >(130, 131, 143, 142, 144,130, 132,141,130.50,1...

Updating selected area in OnPaint in a dialog based application
I have a rather difficult to explain issue and would try again if the information below is unclear. We have a dialog based application which consists of a few controls (3/4th screen) and a graphical area (1/4th screen). My job is to implement selection of any part of the screen and then either paste it in a document or print to the printer. I am able to implement selection of any part of the client area and then either copying it to the clipboard or sending it to printer. I have a problem with maintaining the selection after the window is re-painted for any reason. I am basically using PatBl...

What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...

Conditional formats won't print
I am using Excel 2003 (11.6355.6360) SP1 I have a spreadsheet with cells conditionally formatted to turn green or red. Everything looks fine on the screen...but when I print, I don't see any of the colors..... The cells show up without any color formatting (ie they look just like any other non-conditionally formatted cell). Not sure what has changed b/c I've printed conditionally formatted spreadsheets in color in the past. Anyone have any ideas? "consumer" wrote: > I am using Excel 2003 (11.6355.6360) SP1 > > I have a spreadsheet with cells conditional...

IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...

Sum(IIF statement
I have a large table that I want to query 2 things from. Please be patient because I am pretty new at this. I need to count the number of times that something appears in some fields in the table. Below is what I have tried (keep in mind there are many more fields to count)... test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not Null,1,0)) I would like my output to look like: KEY TEST 1 2 2 0 3 5 etc... And then I want to look in each of those same fields and if there is data concatenate it into one field on the query output and...

Can the IF() function loop through cells looking for a certain condition?
Let me first explain what I am trying to accomplish: I have 2 sheets; one named "Jobs", and the other named "Requests". Now on the "Jobs" sheet, each row contains a job, and the last column of each row, I enter an "R", if that job was a request. Here's an example of the "Jobs" sheet data: ------------------------------------------------- Date Invoice# Customer Name Request? 05/19/04 543218 Larry R 05/19/04 987345 Moe 05/19/04 343529 Fred R 05/19/04 652434 Bubba ...

GETPIVOTDATA from OLAP cube
I am trying to build relative references to an SSAS OLAP cube using the GETPIVOTDATA function (excel 2007). There is a field in the pivot table named "Item". I have grouped this field and named it "Item1". I renamed the values in the grouped field so they are more descriptive than "Group1", etc. For example, I renamed "Group6" to "NBD". When I create the initial formula by typing "=", arrowing onto a field in the cube and pressing enter, I get the expected result and a formula such as this: =GETPIVOTDATA("[Measu...

How do I filter the cells based in Fill color?
In excel when we highlighted some of the cells with color. Finally those unique colored cells want to filter in single step. See http://www.ozgrid.com/VBA/ReturnCellColor.htm -- Regards Dave Hawley www.ozgrid.com "Ravi" <Ravi@discussions.microsoft.com> wrote in message news:EDF851F4-4A3B-4BEF-BCBE-07197B9EC352@microsoft.com... > In excel when we highlighted some of the cells with color. Finally those > unique colored cells want to filter in single step. You could update to xl2007. This feature was added with that version. And if you're usi...

help with nested iif statement
I have a table and I want to create a query that has a new field " Type of Shipment" base on a field "Item Category" If the item category field equals ZDIR, ZTRN ...etc up to 23 different item categories the "Type of Shipment field will equal DIRECT else I would like it to return "Stock" Any help will be greatly appreciated. Thanks Hi, for the iif statement. What you can do is: IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','') Having a long nested if is a headache. What i can suggest ...

Using a query as base for report
Hello, I have created a table where I run a query picking the columns I want in the query. From the query, I create a report. My question is this: Whenever I open the report, I want it to show only the open items. Example: My query has 10 columns, when ALL the fields are populated, the record is complete and does not need to be in the report (i.e. open items report). If the 10 fields are NOT all completed, it's still considered opened and will be on the report when it is run. I think I'm thinking too much about it and making it sound harder to myself. Can you please...

Conditional graphics
Hi there, can someone come to my rescue again? I have a report that I produce, I need to have a different picture on the report depending on the outcome of a criteria. The criteria is a letter B, M, J, F and depending on which one is picked depends on which graphic I need displaying. All the graphics are stored on a sheet separate to the report. I've so far named each graphic and tried to =name1 or =name2 but all I get in the cell is Picture1 or picture2. I have got some VBA in the spreadsheet to change stuff on the report before printing so I can copy and paste the graphic in the V...

Field Level Security
I am trying to setup Field Level Security. However, when I go to Tools>>Setup>>System I do not see it as an option. Security is enabled and I am logged in as "sa." This was an extra cost item. Did you pay for it? Are you running the Business Ready licensing Advanced Management version of GP? MikeWal wrote: Field Level Security 02-Jul-10 I am trying to setup Field Level Security. However, when I go to Tools>>Setup>>System I do not see it as an option. Security is enabled and I am logged in as "sa." Previous Posts In This T...

#N/A conditional formatting
can conditional formatting be set to change the colour if an error is returned as teh cell value. i.e. I want to make #N/A cells be light grey thanks Pete One way: Make your condition (for cell A1): Formula Is =ISNA(A1) In article <131b601c3f6f6$e9bc8620$a601280a@phx.gbl>, "Pete" <anonymous@discussions.microsoft.com> wrote: > can conditional formatting be set to change the colour if > an error is returned as teh cell value. i.e. I want to > make #N/A cells be light grey > thanks > Pete Hi just enter the formula =NA() in the conditional forma...