Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count for items>0 (2002)

    Hi,

    I am trying to find the count of a field, where the value in the field is greater than zero. When I use =count([snvisits]) I get the total entries for the field which is 92. Only 81 of the entries has a value greater than 0. When I tried =count([snvisits]>0) I still get 92. When I tried
    =count([snvisits]>"0") I got an error. I'm not sure what else to try.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Count for items>0 (2002)

    Are you trying to display this count on a form (or report) ? If so you could add a calculated field to the underlying query:

    visits: iif([snvisits]>0,1,0)

    Suming this new field would give you the count you want.
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count for items>0 (2002)

    Hi John,

    That worked great! Best part is that after studying it a bit I finally "got" what it was saying in IF/THAN language. One more question, if I were to have more than one condition, or and IF/Elseif kind've thing, what would the format be?

    Thanks,
    Leesha

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Count for items>0 (2002)

    The syntax of an iif is already an If Then Else
    So visits: iif([snvisits]>0,1,0)
    Means if [snvisits]>0 then visits =1 else visits =0

    One IIF statement can only give two outcomes. If you wanted more than two outcomes, you need to nest more than one IIF

    e.g visits : iif([snvisits]>10, 5, iif([snvisits]>0,1,0))

    this means if [snvisits]>10 then visits =5, else if [snvisits]>0 then visits =1, else visits =0
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count for items>0 (2002)

    Thanks John! It was the info on "nest" statements and the syntax for how to write them that I needed.

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •