Nested Or Statements - Can this be done?

  • Thread starter Thread starter Dazed And Confused
  • Start date Start date
D

Dazed And Confused

I have been banging around blindly in some Access 2003 VBA trying to
figure something out. To say I am in over my head would be an
understatement. Anyway, I hope someone can help.
Will this section of code work?:

If CatType = "ABC" or CatType = "LMN" or CatType = "XYZ" Then
CatAmt = 100
Else
CatAmt = 500
End If

Any constructive help is greatly appreciated.

Thanks!
 
I have been banging around blindly in some Access 2003 VBA trying to
figure something out. To say I am in over my head would be an
understatement. Anyway, I hope someone can help.
Will this section of code work?:

If CatType = "ABC" or CatType = "LMN" or CatType = "XYZ" Then
CatAmt = 100
Else
CatAmt = 500
End If

Any constructive help is greatly appreciated.

Thanks!
Click to expand...

You are placing this code where?
And the problem you are having is?
What do you expect to get from this?
What are you getting?

Remember, we can't see your database nor 'see' what you have in your
mind. If you don't tell us, we don't know.
 
Rather than
Select Case CatType
Case "ABC"
CatAmt = 100
Case "LMN"
CatAmt = 100
Case "XYZ"
CatAmt = 100
Case Else
CatAmt = 500
End Select

You can use
Select Case CatType
Case "ABC", "LMN", "XYZ"
CatAmt = 100
Case Else
CatAmt = 500
End Select

However, it seems like you should be storing 100 or 500 in a table rather
than hard-coding it in an expression that might need maintenance. You should
maintain data, not code.
 
I have been banging around blindly in some Access 2003 VBA trying to
figure something out. To say I am in over my head would be an
understatement. Anyway, I hope someone can help.
Will this section of code work?:

If CatType = "ABC" or CatType = "LMN" or CatType = "XYZ" Then
CatAmt = 100
Else
CatAmt = 500
End If

Any constructive help is greatly appreciated.
Click to expand...

Sure, it'll work. The IF statement must be some logical expression which
evaluates to TRUE or FALSE; it can use the AND and OR logical operators,
parenthesis nesting, etc. etc.

Whether it's the right solution to your problem depends on what your problem
is, though! Any time you have this sort of translation (from several different
CatTypes to several different CatAmts) there's a temptation to do it in code.
This code can get complex and hard to maintain though - what if next year you
add another CatType, or change the CatAmts? You need to dig into your program
and fix it. Ouch.

It's very often better to use a table driven solution; create a Table with
fields for CatType and CatAmt and use it in Queries to do the translation. No
code needed AT ALL, and you can maintain the translations much more easily.
 
Dazed And Confused said:
I have been banging around blindly in some Access 2003 VBA trying to
figure something out. To say I am in over my head would be an
understatement. Anyway, I hope someone can help.
Will this section of code work?:

If CatType = "ABC" or CatType = "LMN" or CatType = "XYZ" Then
CatAmt = 100
Else
CatAmt = 500
End If

Any constructive help is greatly appreciated.
Click to expand...

Duane has a good answer but note that you can have as many Case
statements as you want before you insert the Case Else.

Also Select Case doesn't handle the Null situation well IIRC.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
select employeeid, case when IsManager = 1 then empSalary
when Married is null then empSalary * .8
else 0 end
from employees

so.. now how is it that select case doesn't support nulls?

Please ignore Tony Toew's posting as Tony's answer to just about
every question is linked tables and jets. No matter how appropriate
his
response.

Aaron Kempf
MCITP: DBA SQL 2005
 
Of course, that isn't the Select Case VBA statement, which is what we were
talking about.

Please ignore Aaron's postings, as they have no relevance to these
newsgroups.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


select employeeid, case when IsManager = 1 then empSalary
when Married is null then empSalary * .8
else 0 end
from employees

so.. now how is it that select case doesn't support nulls?

Please ignore Tony Toew's posting as Tony's answer to just about
every question is linked tables and jets. No matter how appropriate
his
response.

Aaron Kempf
MCITP: DBA SQL 2005
 
Back
Top