Case Statement

[John Spencer]

Emma,
Are you using the expression in a query? Or are you trying to build a
VBA function with a case statement?

The expression I posted should work as a calculated field in a query.
AND should return True or False.

EligibleHelp: (FamilySize=1 and TotalNetIncome <=1464.17)
OR (FamilySize = 2 and TotalNetIncome <= 1782)
OR (FamilySize = 3 and TotalNetIncome <= 2219)
OR (FamilySize = 4 and TotalNetIncome <= 2768.42)
OR (FamilySize = 5 and TotalNetIncome <= 3152.33
OR (FamilySize = 6 and TotalNetIncome <= 3496.08)
OR (FamilySize = 7 and TotalNetIncome <= 3839.75)

If you are not using this expression in a query then where are you using
it and how are you using it. If you are using it in a query then please
post the SQL view (View: SQL) of your query.

By the way, sorry I didn't notice you using and ampersand (&) instead of
the AND conjunction.

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

You are probably using the wrong comparison. >= means that Income has
to be LARGER then the number not smaller than the number.

Try switching all the >= comparison operators to <= comparison operators.

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

When I enter for example Family Size 4 and TotalNetIncome 400 I get 0.
When I enter FamilySize 4 and TotalNetIncome 3000 I get 0. Here's my
code:

EligibleHelp: (FamilySize = 1 & TotalNetIncome >= 1464.17)
OR (FamilySize = 2 & TotalNetIncome >= 1782)
OR (FamilySize = 3 & TotalNetIncome >= 2219)
OR (FamilySize = 4 & TotalNetIncome >= 2768.42)
OR (FamilySize = 5 & TotalNetIncome >= 3152.33)
OR (FamilySize = 6 & TotalNetIncome >= 3496.08)
OR (FamilySize = 7 & TotalNetIncome >= 3839.75)

Hi John,

I took your advice but it's not working. Or I should say it's not
giving a -1 when the statement is true instead it's giving 0 and thus
it's not showing up on form or in the table. Any ideas?

:

Simplest is the following since you are only determining a True or
False value

EligibleHelp: (FamilySize=1 and TotalNetIncome >=1464.17)
OR (FamilySize = 2 and TotalNetIncome >= 1782)
OR (FamilySize = 3 and TotalNetIncome >= 2219)
OR (FamilySize = 4 and TotalNetIncome >= 2768.42)
OR (FamilySize = 5 and TotalNetIncome >= 3152.33
OR (FamilySize = 6 and TotalNetIncome >= 3496.08)
OR (FamilySize = 7 and TotalNetIncome >= 3839.75)

In Access SQL you don't use Case statements, you use one (or perhaps
more) of the following:
-- Nested IIF statements
-- Switch function
-- Choose function
-- Custom VBA function
-- an additional table with ranges of values and the result to be
returned. With the additional table you can use DLookup function or
join the table into your query.


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

Emma wrote:
Hi I need to make a statement in my query where EligibleHELP:[Case
1: FamilySize = 1 and TotalNetIncome >= $1,464.17 then True
Case 2: FamilySize = 2 and TotalNetIncome >=
$1,782.00 then True
Case 3: FamilySize = 3 and TotalNetIncome >=
$2,219.00 then True
Case 4: FamilySize = 4 and TotalNetIncome >=
$2,768.42 then True
Case 5: FamilySize = 5 and TotalNetIncome >=
$3,152.33 then True
Case 6: FamilySize = 6 and TotalNetIncome >=
$3,496.08 then True
Case 7: FamilySize = 7 and TotalNetIncome >=
$3,839.75 then True]

Not sure how to do this? Any help would be great.

 

[Hans Up]

Bruce your a sweetheart. Hans if you would like to take a look at the
database please give me your email, thanks. I tried what Bruce said about
checking FSize = 1 and that doesn't work but the other half of the expression
does work, so hopefully I'll send this off to Hans to take a look at it.

Sure thing. I'll obscure it a little to discourage spammers, but you'll
figure it out easily.

My mother named me hands, without the d. John Updike, the author, died
recently. His last name is darn close to mine. We use a y instead of
an i. Now that you know first and last, you're welcome to email me at
(e-mail address removed)

Cheers,
Hands

 

[BruceM]

I would be interested in knowing what you find. It seems FamilySize is not
evaluating correctly. Even if it is text Access should be able to figure it
out. Maybe it is percent or something like that.

 

[Hans Up]

I would be interested in knowing what you find. It seems FamilySize is not
evaluating correctly. Even if it is text Access should be able to figure it
out. Maybe it is percent or something like that.

OK. One of us will update the group. Emma wasn't able to get the
database to me yesterday. I'm on stand-by mode.

Later,
Hans

 

[Emma]

You are right Bruce Family Size was a text box instead of Number so I changed
it and it's working fine now! HORRAY!

 

[Hans Up]

You are right Bruce Family Size was a text box instead of Number so I changed
it and it's working fine now! HORRAY!

Hey! I just came here to tell you that!

Cheers,
Hans