IFF Statement

[Guest]

Hi All,
Please assist, when it comes to statements i am an idiot.
I need a statement that will add values together in a textbox.

It goes like this...
If 0-33 then 0
34-49 then 10
50-69 then 15
70-100 then 20

plus

if single then 30

plus

years of service
if 0 - 5 then 10
5-10 then 15
10> then 20

As well as
If amount is
<80 000 then 30

80 000 < 100 000 then 20
100 000 < 121 000 then 15
121 000 then 0

These are all different fields in my table, i need all of them scored in one
textbox, as per above

 

[JesseAviles]

Hi All,
Please assist, when it comes to statements i am an idiot.
I need a statement that will add values together in a textbox.

It goes like this...
If 0-33 then 0
34-49 then 10
50-69 then 15
70-100 then 20

plus

if single then 30

plus

years of service
if 0 - 5 then 10
5-10 then 15
10> then 20

As well as
If amount is
<80 000 then 30


These are all different fields in my table, i need all of them scored in one
textbox, as per above

One way you can do it is by code.

In the form's module create a new function something like:

Private Function fctSumOfValues() as long
on error goto ErrorHandler

dim lngValue as long

fctSumOfValues = 0

If field1 >= 0 and field1 <= 33 then
lngValue = 0
ElseIf field1 >33 and field1<=49 then
lngValue = 10
'... and so on
Else
Goto Exithandler
End if

If field2 = single then
lngValue = 30 + lngValue
End if
'... and so on

fctSumOfValues = lngValue

ExitHandler:
end function

ErrorHandler:
msgbox "Error: " & Err.number & vbnewline & Err.description &
vbnewline & " In fctSumOfValues."
resume exithandler
end function

The function will return the sum of all the values you want once the
proper information has been entered. The function can be called from
a command button that will set the value of the text box to the value
of the function. the above can be written with Select case statements
which I believe will be easier to manage.

 

[Guest]

Hi

Sorry but it won't work like this. You can have only 7 nested iif's in one.

Like this

If something is true then ABC (thats 1 iif) if it's not true then if
something else is true then XYZ (thats the second iif) if something else and
something are both not true then if whatever is true then DEF (thats the 3rd
iif), etc.etc.etc.

By my count you need 12.

Press F1 and search on nested iif for more info.

If it were me I may use iif's but in seperate formulas and then add the
results. Although it seems to me that this whole idea is wrong as there are
too many varibles with this. But you know your DB and I don't so you may be
right but I would look at the raw data and see if you can amend it.

If you still want to use an iif some thing like this would work

I have assumed that you are refering to field called [SomeField] and that it
is formated for numbers

It goes like this...
If 0-33 then 0
34-49 then 10
50-69 then 15
70-100 then 20

IIf ( [SomeField] BETWEEN 0 AND 33, 0, IIf ( [SomeField] BETWEEN 34 AND 49 ,
10, IIf ( [SomeField] BETWEEN 50 AND 69, 15, IIf ( [SomeField] BETWEEN 70 AND
100, 20, ???? ) ) )

Note - this assumes there are no Null fields
If there "may" be then use
IIf (Nz( [SomeField]) BETWEEN 0 AND 33, 0, IIf (Nz( [SomeField]) ...etc.etc

Also what happens if the results are more than 100 (see above the space with
????) and also what if the result is less than 0.
You can use the same process for each of your "iif's" and then simply add
the results to gether.

Hope this helps