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" wrote:

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

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" wrote:

> 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" wrote:
>
> > 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.
> >

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

Emma wrote:
> 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" wrote:
>
>> 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" wrote:
>>
>>> 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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> Emma wrote:
>> 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" wrote:
>>
>>> 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" wrote:
>>>
>>>> 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.

Emma wrote:
> 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)

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#

"Hans Up" wrote:

> Emma wrote:
> > 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)
>
> Use the word "and" in place of the ampersand character (as John showed
> you), like this:
>
> FamilySize = 1 And TotalNetIncome >= 1464.17
>

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

"Emma" wrote:

> Hi when I enter the <= I get all -1's including larger and smaller numbers.
> When I use the And I get an Error#
>
> "Hans Up" wrote:
>
> > Emma wrote:
> > > 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)
> >
> > Use the word "and" in place of the ampersand character (as John showed
> > you), like this:
> >
> > FamilySize = 1 And TotalNetIncome >= 1464.17
> >

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.

"Emma" wrote:

> Hi when I enter the <= I get all -1's including larger and smaller numbers.
> When I use the And I get an Error#
>
> "Hans Up" wrote:
>
> > Emma wrote:
> > > 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)
> >
> > Use the word "and" in place of the ampersand character (as John showed
> > you), like this:
> >
> > FamilySize = 1 And TotalNetIncome >= 1464.17
> >

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