On May 14, 9:18 am, Frikkels <Frikk...@discussions.microsoft.com>
wrote:

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.

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

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