455,484 Members | 1,803 Online 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
3 Replies

 P: n/a On 6 Nov 2005 00:52:53 -0800, "Chesne" wrote: I'm trying to use IIF and having hasseles.The statement below is a generic example of what I am trying to achievebut of course it will not work!IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And18,"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 theones 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" wrote:I'm trying to use IIF and having hasseles.The statement below is a generic example of what I am trying to achievebut of course it will not work!IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And18,"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 theones 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 wrote: Wayne Gillespie wrote: On 6 Nov 2005 00:52:53 -0800, "Chesne" wrote:I'm trying to use IIF and having hasseles.The statement below is a generic example of what I am trying to achievebut of course it will not work!IIf([CountofSurname]>=13,"1") Or IIf([CountofSurname] Between 1 And18,"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 theones 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 and4". 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 tackleit. 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. 