Logical Expressions in Access

[RoadKill]

Okay, I am trying to put together a logical formula similar to what I would
in Excel.

This is what I have. But I want more options. For example, if they have 2
they should be Private E-2. Three should be Private First Class. How can I
add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

Thank you

 

[RoadKill]

Okay, I found it out.

But, is there a limit to the number of qualifcations we can have? 7,10,12?

 

[fredg]

Okay, I am trying to put together a logical formula similar to what I would
in Excel.

This is what I have. But I want more options. For example, if they have 2
they should be Private E-2. Three should be Private First Class. How can I
add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

Thank you

IIf statements can be nested.
IIf([CountofProduct]=1,"Private",IIf([CountofProduct]=2,"Private
E-2",IIf([CountofProduct]=3,"SomeOtherValue","DefaultValue")))

Note, nested IIf's can get cumbersome. Look up the Choose() function,
the Switch() function and the Select Case statement in VBA help for
alternatives

 

[Bob Barrows]

Okay, I found it out.

But, is there a limit to the number of qualifcations we can have?
7,10,12?

Okay, I am trying to put together a logical formula similar to what
I would in Excel.

This is what I have. But I want more options. For example, if they
have 2 they should be Private E-2. Three should be Private First
Class. How can I add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

This is not Excel*. This information should be stored in a table, not in
an expression. Two columns:

CountOfProduct Number
Rank Text

1 | Private
2 | Private E-2
3 | Private E-3
etc.

Then a simple inner join in a query, or a DLookup(), or a simple VBA
function can get you the rank.


*Even in Excel, you would be better off using VLookup for this rather
than a long set of nested IFs.

 

[John Spencer]

I would normally use a small table instead.

RankNumber
RankTitle

And then join that table to another table. That gives you unlimited rank
numbers and the ability to globally change Rank Title by modifying the entry
in the table. If you use embedded expressions, you are going to have to
change every place you used that expression.

You can use the SWITCH expression or the Choose expression or Nested IIF
expression.

Yes, there is a limit using all of those.

Choose([CountOfProduct],"Private","Private E-2","Private First Class")

Switch(CountOfProduct=1,"Private",CountOfProduct=2,"Private
E-2",CountOfProduct=3,"Private First Class")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Okay, I found it out.

But, is there a limit to the number of qualifcations we can have? 7,10,12?

Okay, I am trying to put together a logical formula similar to what I would
in Excel.

This is what I have. But I want more options. For example, if they have 2
they should be Private E-2. Three should be Private First Class. How can I
add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

Thank you

 

[RoadKill]

True. Guess I was just determined to use an expression

Okay, I found it out.

But, is there a limit to the number of qualifcations we can have?
7,10,12?

Okay, I am trying to put together a logical formula similar to what
I would in Excel.

This is what I have. But I want more options. For example, if they
have 2 they should be Private E-2. Three should be Private First
Class. How can I add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

This is not Excel*. This information should be stored in a table, not in
an expression. Two columns:

CountOfProduct Number
Rank Text

1 | Private
2 | Private E-2
3 | Private E-3
etc.

Then a simple inner join in a query, or a DLookup(), or a simple VBA
function can get you the rank.


*Even in Excel, you would be better off using VLookup for this rather
than a long set of nested IFs.

--
HTH,
Bob Barrows


.

 

[David Z]

Since you are determined to use an expression, try passing the value into a
Case Statement and returning the string you want. Since there is a finite
list of ranks, and no reason to need to alter the strings, it would be a one
time fix.

In a query, you would pass the number value like this:

Rank: CheckRank(NumberValue)

The function would be:

Public Function CheckRank(NumberValue) As String
Select Case NumberValue
Case Is = 1
CheckRank = "Private"
Case Is = 2
CheckRank = "Private E-2"
Case Is = 3
CheckRank = "Private First Class"
End Select
End Function

The advantage to the Select Case function is that nested IIFs become
cumbersome quickly and difficult to debug. Also, the IIF statement will
evaluate each and every possibility, whereas the Select Case stops when it
finds something that matches the Case. I have gained speed by ordering the
Select Case choices in the order of the ones that are used the most.

Good luck.

David

True. Guess I was just determined to use an expression

Okay, I found it out.

But, is there a limit to the number of qualifcations we can have?
7,10,12?

:

Okay, I am trying to put together a logical formula similar to what
I would in Excel.

This is what I have. But I want more options. For example, if they
have 2 they should be Private E-2. Three should be Private First
Class. How can I add multiple qualifications in Access expressions.

IIf([CountofProduct]=1,"Private","Private E-2") AS Rank

This is not Excel*. This information should be stored in a table, not in
an expression. Two columns:

CountOfProduct Number
Rank Text

1 | Private
2 | Private E-2
3 | Private E-3
etc.

Then a simple inner join in a query, or a DLookup(), or a simple VBA
function can get you the rank.


*Even in Excel, you would be better off using VLookup for this rather
than a long set of nested IFs.

--
HTH,
Bob Barrows


.

 

[John W. Vinson]

Since you are determined to use an expression, try passing the value into a
Case Statement and returning the string you want. Since there is a finite
list of ranks, and no reason to need to alter the strings, it would be a one
time fix.

In a query, you would pass the number value like this:

Rank: CheckRank(NumberValue)

The function would be:

Public Function CheckRank(NumberValue) As String
Select Case NumberValue
Case Is = 1
CheckRank = "Private"
Case Is = 2
CheckRank = "Private E-2"
Case Is = 3
CheckRank = "Private First Class"
End Select
End Function

The advantage to the Select Case function is that nested IIFs become
cumbersome quickly and difficult to debug. Also, the IIF statement will
evaluate each and every possibility, whereas the Select Case stops when it
finds something that matches the Case. I have gained speed by ordering the
Select Case choices in the order of the ones that are used the most.

Good luck.

David

Good advice. Note that you can go one step simpler, without the need for any
VBA code at all, using either the Choose() or Switch() builtin functions.

 

[David W. Fenton]

Note that you can go one step simpler, without the need for any
VBA code at all, using either the Choose() or Switch() builtin
functions.

Any time I think I need a CASE SELECT in SQL, it's a pretty good
clue that the data being retrieved probably ought to be stored in a
table.