Results 1 to 4 of 4
  1. #1
    seth.murphine is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    charlotte nc
    Posts
    26

    Count Iif statement

    I have an sql statement



    Code:
    sum(iif(instr([yp_adv_data].[adRevCol],0),0,1))
    If adRevCol is 0.00 it returns 0, if it is 10.00 it continues to return zero. If it is 16.66 it will return 1. Is this because I don't have proper formatting in the sql? Because I need it to return 1 as long as there is an amount greater than 0.00.

    Thanks for any help.

  2. #2
    seth.murphine is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    charlotte nc
    Posts
    26
    Now that I'm doing some reading it most likely has to do with the instr function and because 0 is in the string. So I guess I need a different function.

  3. #3
    Join Date
    Dec 2009
    Posts
    7
    Hi

    I do not quite understand the purpose. Verify that has at least one zero at any position?

    sum(iif(instr([yp_adv_data].[adRevCol],0) > 0,0,1))

  4. #4
    seth.murphine is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    charlotte nc
    Posts
    26
    Avelino,

    Thank you, what I love about Access is the mutliple ways of doing the same thing.

    So before I saw your answer I came up with my own

    I wrote the following expression:
    Code:
    -Sum([SumOfadRevCol]>0)
    Worked like a charm, if you look through my posts you'll notice that sometimes I don't think I have the answer and then enlightenment happens.

    Thanks for the help though.

    Respect,
    Seth

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count Ifs
    By samthomasny in forum Queries
    Replies: 3
    Last Post: 02-02-2012, 02:36 PM
  2. Count if (where?)
    By jvlajcic in forum Queries
    Replies: 1
    Last Post: 12-14-2011, 03:09 PM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. Count on ID
    By dssrun in forum Access
    Replies: 4
    Last Post: 07-26-2011, 11:45 AM
  5. Sum the Count
    By Adele in forum Queries
    Replies: 5
    Last Post: 07-26-2011, 06:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums