7 Replies Latest reply: Jun 13, 2018 7:11 AM by Anthony Richardson

# counting records between two values

i need to count the records between two values, heres what i got and its erroring on me

count(if((mid([PIID],10,1)<>'G') and ([Base And Alloptions Amnt]'>150000<=50000') and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID],6)),[Contr Office ID]))

can someone please tell me what i'm doing wrong.

thank you

• ###### Re: counting records between two values

May be try this

count(if((mid([PIID],10,1)<>'G') and ([Base And Alloptions Amnt] > 150000 and [Base And Alloptions Amnt] <=50000') and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID],6)),[Contr Office ID]))

or using set analysis

Count({<PIID -= {"?????????G*"}, [Base And Alloptions Amnt] ={">150000<=50000"}, [Mod Number] = {'0'}, [Contr Office ID] = {"=[Contr Office ID] = Left([RefIDV PIID], 6)"}>} [Contr Office ID])

• ###### Re: counting records between two values

i'm not familar with set analysis, so for now i would need to stick with the first example you provided. however it seems like when the record is less than 500K it includes those values that are under 150K and the opposite happens when it greater than 150K it includes values greater that 500K so i'm cound records that i shouldn't

that is why i'm trying to figure out the > or <= in my example.

thanks

• ###### Re: counting records between two values

What exactly is the condition you are looking for [Base And Alloptions Amnt]?

• ###### Re: counting records between two values

i need to count all records that are > 150000 and are <= 500000

• ###### Re: counting records between two values

oh and yes [Base and Alloptions Amnt] is the field

• ###### Re: counting records between two values

Try this

Count(If((Mid([PIID], 10, 1) <> 'G') and ([Base And Alloptions Amnt] > 150000 and [Base And Alloptions Amnt] <=500000) and ([Mod Number] = 0) and ([Contr Office ID] <> left ([RefIDV PIID], 6)), [Contr Office ID]))

• ###### Re: counting records between two values

it works thank you!!! you ROCK!