Case Statement

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

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.
 
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?

John Spencer MVP said:
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
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.
Click to expand...
Click to expand...
 
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)



Emma said:
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?

John Spencer MVP said:
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
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.
Click to expand...
Click to expand...
Click to expand...
 
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)



Emma said:
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?

John Spencer MVP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
The OP got the same result (false) with either 400 or 3000, one of which is
smaller and the other larger than the target number 2768.42.

To the OP, are Family Size and TotalNetIncome number (or currency) fields in
the table? Try adding two fields to the query:
FSize: [FamilySize]
TNI: [TotalNetIncome]

Run the query and see if you are getting the expected values.

John Spencer MVP said:
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)



Emma said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Emma said:
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)
Click to expand...

Use the word "and" in place of the ampersand character (as John showed
you), like this:

FamilySize = 1 And TotalNetIncome >= 1464.17
 
Hi when I enter the <= I get all -1's including larger and smaller numbers.
When I use the And I get an Error#
 
When I tried adding the FSize: [FamilySize]
TNI: [TotalNetIncome]
They came up with the correct values.
Here's my code which isn't working just producing -1's

EligibleHelp:
(FamilySize = 1 & TotalNetIncome <= CCur(1464.17))
OR (FamilySize = 2 & TotalNetIncome <= CCur(1782))
OR (FamilySize = 3 & TotalNetIncome <= CCur(2219))
OR (FamilySize = 4 & TotalNetIncome <= CCur(2768.42))
OR (FamilySize = 5 & TotalNetIncome <= CCur(3152.33))
OR (FamilySize = 6 & TotalNetIncome <= CCur(3496.08))
OR (FamilySize = 7 & TotalNetIncome <= CCur(3839.75))
 
Ok so now it appears to be working though it isn't when I put a large number
like 3000 TotalNetIncome for 4 Family Size I get a blank instead of a 0. But
if I put in a small number like 400 it goes to -1 then if I put in 3000 it
does nothing and stays at -1 so it's always on. I can't change the
TotalNetIncome to a higher value.
 
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even though
there's no TotalNetIncome entered. Also once it's true I can't get it to
change to False no matter how large a number I put in.
 
Emma said:
Hi when I enter the <= I get all -1's including larger and smaller numbers.
When I use the And I get an Error#
Click to expand...

Hi again, Emma.

I think you have more than one problem if you're getting an error after
substituting "And" for "&"

The ampersand is the concatenation operator --- it joins two string
expressions together.

What you need is the Boolean "And" comparison. As in:
if condition1 = True AND condition2 = True, then do what you want done
when both are true.

Perhaps it's not clear because the common pattern is to shorten it to:
if condition1 AND condition2 ...
(You don't need to explicitly include the "= True" piece; it's assumed.)

At this point, let me show you a session I ran in the Immediate Window
which I hope will clarify everything for you:

? 1 < 2
True
? 1 > 2
False
? (1 < 2) And (1 > 2)
False
? (1 < 2) & (1 > 2)
TrueFalse

See what happened with that last one? Concatenation produced a string
which joined the string equivalents of the separate True and False
boolean values. That string is not a boolean value.

I don't know what else to tell you. If it's still not clear, I can only
ask you to trust me when I tell you that you will never find joy while
you continue to use "&" instead of "And" in this situation.
 
Good observatio! It seems obvious now that you point it out the ampersands.
I feel pretty silly for not noticing that.
 
Hi Bruce, here's my AND code:

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))

Emma said:
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even though
there's no TotalNetIncome entered. Also once it's true I can't get it to
change to False no matter how large a number I put in.

Emma said:
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.
Click to expand...
Click to expand...
 
Emma said:
Hi Hans,

I have tried AND And and, I'm always getting an #ERROR
Click to expand...
c
Hi Emma,

Surely you must be frustrated. I'm sorry for being dramatic last time,
but I wanted you to pay special close attention.

We need to figure out what's causing #ERROR, since "And" isn't the culprit.

Would you try building a new query which includes that expression and
see can it work there?

I don't what else to do just now, but I aint give up yet.

If you're willing to make a copy of your database in 2002-2003 format,
create a zip archive of it, and email it to me ... I'll be glad to look
it over. Discard/obfuscate any sensitive/confidential data. If you
could, discard any database objects which aren't relevant to the current
problem. If you want to do that, I'll give you my email address.

TTFN,
Hans
 
I hate to say it but I think I was getting further ahead this morning when I
used 7 Eligible's in the query instead of this stupid long one which doesn't
do jack.

Emma said:
Hi Bruce, here's my AND code:

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))

Emma said:
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even though
there's no TotalNetIncome entered. Also once it's true I can't get it to
change to False no matter how large a number I put in.

Emma said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Here's something else to try. Try adding new fields again, but this time so
there is a True or False:
FSize: [FamilySize] = 1
TNI: [TotalNetIncome] < 1464.17

Use whatever literal values you like. The idea is to test whether the two
halves of the expression are yielding the correct True/False values.

Emma said:
Hi Bruce, here's my AND code:

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))

Emma said:
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even
though
there's no TotalNetIncome entered. Also once it's true I can't get it to
change to False no matter how large a number I put in.

Emma said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Hans offered, in another part of the thread, to take a look at your
database. I have to leave now, but I will look at this thread again
tomorrow. If you get it figured out, please let me know how you solved the
problem. If the problem remains, we'll get it figured out somehow.

Emma said:
I hate to say it but I think I was getting further ahead this morning when
I
used 7 Eligible's in the query instead of this stupid long one which
doesn't
do jack.

Emma said:
Hi Bruce, here's my AND code:

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))

Emma said:
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even
though
there's no TotalNetIncome entered. Also once it's true I can't get it
to
change to False no matter how large a number I put in.

:

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.
Click to expand...
Click to expand...
Click to expand...
 
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.

BruceM said:
Here's something else to try. Try adding new fields again, but this time so
there is a True or False:
FSize: [FamilySize] = 1
TNI: [TotalNetIncome] < 1464.17

Use whatever literal values you like. The idea is to test whether the two
halves of the expression are yielding the correct True/False values.

Emma said:
Hi Bruce, here's my AND code:

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))

Emma said:
It seems to always come up as true because once I leave the FamilySize
textbox after enetering a number the Eligible field becomes true even
though
there's no TotalNetIncome entered. Also once it's true I can't get it to
change to False no matter how large a number I put in.

:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top