Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
IIF Function
- Thread starter KatGraham
- Start date
dschomburg
MIS
Kat,
Not sure if this is what you're looking for but maybe the SelectCase would work bette for you.
This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.
Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case Is > 8 And Number < 11 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select
Not sure if this is what you're looking for but maybe the SelectCase would work bette for you.
This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.
Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case Is > 8 And Number < 11 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select
- Thread starter
- #3
- Thread starter
- #5
Ok, here's the deal:
I have a form that has:
txtProduct = LTD, STD, BOTH
txtStatus = combo box of All, Open, Closed etc.
txtIOP = combo box of Y or N
I have a report that has a TEXT box that I want to display txtProduct and "Exclude ...." if txtOIP equals Y. The difficult part is that this box should only display the "Exclude..." portion if txtStatus is Open.
I can't seem to get a case statement to work when I set it up in a module or in the report.
I have a form that has:
txtProduct = LTD, STD, BOTH
txtStatus = combo box of All, Open, Closed etc.
txtIOP = combo box of Y or N
I have a report that has a TEXT box that I want to display txtProduct and "Exclude ...." if txtOIP equals Y. The difficult part is that this box should only display the "Exclude..." portion if txtStatus is Open.
I can't seem to get a case statement to work when I set it up in a module or in the report.
without quite knowing all the details, you can do a
iff(txtOne=1 or txtOne=2.....). Or and And statements work the same way. I have done it in the past and it works. The only problem I see if the statement gets too long sometimes Access doesn't like it.
iff(txtOne=1 or txtOne=2.....). Or and And statements work the same way. I have done it in the past and it works. The only problem I see if the statement gets too long sometimes Access doesn't like it.
Alternatively you could try using the CHOOSE function. That is if your options are sequential numbers starting at 1.
e.g answer = choose(columnvalue, valuefor1, valuefor2, valuefor3,....)
in your instance
NameOfLabel.Caption = choose(txtStatus,"B","A","B","B","A","A","B"
Even better still if you use a combobox and put the status values in a table you can put some code in the combobox's AfterUpdate event to set the Caption. e.g.
NameOfLabel.Caption = cbStatus.column(2)
If the combobox has 3 columns (the index starts at 0) and the first is the Code for the Status i.e 1,2,3... and the second is the description and the third is the value you want tot set the caption to.
e.g answer = choose(columnvalue, valuefor1, valuefor2, valuefor3,....)
in your instance
NameOfLabel.Caption = choose(txtStatus,"B","A","B","B","A","A","B"
Even better still if you use a combobox and put the status values in a table you can put some code in the combobox's AfterUpdate event to set the Caption. e.g.
NameOfLabel.Caption = cbStatus.column(2)
If the combobox has 3 columns (the index starts at 0) and the first is the Code for the Status i.e 1,2,3... and the second is the description and the third is the value you want tot set the caption to.
- Status
Similar threads
- Locked
- Question
- Locked
- Question