counting records between two values

[Anonymous]

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

[sunny_talwar]

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])

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

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]))

[Anonymous]

it works thank you!!! you ROCK!