trouble with the IIF

[Chesne]

I'm trying to use IIF and having hasseles.

The statement below is a generic example of what I am trying to achieve
but of course it will not work!

IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And
18,"1" and "2" ) Or IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3") Or IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4")

In other words if the count is between 1 and 18 then return all the
ones and the twos.

Any help would be appreciate. TIA - R

[Wayne Gillespie]

On 6 Nov 2005 00:52:53 -0800, "Chesne" <as******@paradise.net.nz> wrote:

I'm trying to use IIF and having hasseles.

The statement below is a generic example of what I am trying to achieve
but of course it will not work!

IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And
18,"1" and "2" ) Or IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3") Or IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4")

In other words if the count is between 1 and 18 then return all the
ones and the twos.

Any help would be appreciate. TIA - R

Remove the "Or"s and nest the IIF statements. Each IIF statement becomes the second argument of the previous statement.
Something like - (air code)

IIf([CountofSurname]>=13,"1",IIf([CountofSurname] Between 1 And
18,"1" and "2",IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3",IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4"))))

[Salad]

Wayne Gillespie wrote:

On 6 Nov 2005 00:52:53 -0800, "Chesne" <as******@paradise.net.nz> wrote:

I'm trying to use IIF and having hasseles.

The statement below is a generic example of what I am trying to achieve
but of course it will not work!

IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And
18,"1" and "2" ) Or IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3") Or IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4")

In other words if the count is between 1 and 18 then return all the
ones and the twos.

Any help would be appreciate. TIA - R

Remove the "Or"s and nest the IIF statements. Each IIF statement becomes the second argument of the previous statement.
Something like - (air code)

IIf([CountofSurname]>=13,"1",IIf([CountofSurname] Between 1 And
18,"1" and "2",IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3",IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4"))))

It appears the op wants a message like "1 and 2" or "1 and 2 and 3 and
4". I'm not sure if
"1" and "2"
will work. It might be better with
"1 and 2"

The original statement was so flawed I wouldn't have wanted to tackle
it. Kudus for making sense out of it.

[Wayne Gillespie]

On Sun, 06 Nov 2005 19:09:50 GMT, Salad <oi*@vinegar.com> wrote:

Wayne Gillespie wrote:

On 6 Nov 2005 00:52:53 -0800, "Chesne" <as******@paradise.net.nz> wrote:

I'm trying to use IIF and having hasseles.

The statement below is a generic example of what I am trying to achieve
but of course it will not work!

IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And
18,"1" and "2" ) Or IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3") Or IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4")

In other words if the count is between 1 and 18 then return all the
ones and the twos.

Any help would be appreciate. TIA - R

Remove the "Or"s and nest the IIF statements. Each IIF statement becomes the second argument of the previous statement.
Something like - (air code)

IIf([CountofSurname]>=13,"1",IIf([CountofSurname] Between 1 And
18,"1" and "2",IIf([CountofSurname] Between 1 And 23,"1" and "2"
and "3",IIf([CountofSurname] Between 26 And 32,"1" and "2" and "3"
and "4"))))

It appears the op wants a message like "1 and 2" or "1 and 2 and 3 and
4". I'm not sure if
"1" and "2"
will work. It might be better with
"1 and 2"

The original statement was so flawed I wouldn't have wanted to tackle
it. Kudus for making sense out of it.

You are correct, it should be something like -

IIf([CountofSurname]>=13,"1",IIf([CountofSurname] Between 1 And
18,"1 and 2",IIf([CountofSurname] Between 1 And 23,"1 and 2
and 3",IIf([CountofSurname] Between 26 And 32,"1 and 2 and 3
and 4"))))