By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,484 Members | 1,803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,484 IT Pros & Developers. It's quick & easy.

trouble with the IIF

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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"))))
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

P: n/a
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"))))

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.