Results 1 to 4 of 4
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Summing half a value with full value on an IF

    I'm using Totals: Sum(IIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5th]>0,1,0)) to sum totals in a query and it works great, however I have been told that if the school is SchoolType 3 then it gets a half value (.5) for each ShowTime

    I can't figure out how to sum those that aren't 3 with those with 3. So if a few of the schools are type 3 then they get added to the total but as .5

  2. #2
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    i have tried

    Totals: Sum(IIf([SchoolTypeID = 3, IIf([ShowTime1st]>0,1/2,0)+IIf([ShowTime2nd]>0,1/2,0)+IIf([ShowTime3rd]>0,1/2,0)+IIf([ShowTime4th]>0,1/2,0)+IIf([ShowTime5th]>0,1/2,0),IIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5tIIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5th]>0,1,0)]>0,1,0)))

    with no luck, even placing .5 has problems

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    eh I had missed the closing of SchoolTypeID and went with this

    Totals: Sum(IIf([tblSchools]![SchoolTypeID]=3,IIf([ShowTime1st]>0,0.5,0)+IIf([ShowTime2nd]>0,0.5,0)+IIf([ShowTime3rd]>0,0.5,0)+IIf([ShowTime4th]>0,0.5,0)+IIf([ShowTime5th]>0,0.5,0),IIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5th]>0,1,0)))

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    update - was getting wrong results from someone else to compare to.

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

Similar Threads

  1. Bottom half of form not showing?
    By christm in forum Forms
    Replies: 20
    Last Post: 03-08-2013, 11:11 AM
  2. Half-day leave computation
    By bsvubana in forum Access
    Replies: 5
    Last Post: 11-29-2012, 02:28 AM
  3. Rounding number up to half hour
    By crxftw in forum Forms
    Replies: 2
    Last Post: 08-23-2011, 07:29 AM
  4. Round up time half an hour
    By JBM18 in forum Queries
    Replies: 2
    Last Post: 12-09-2010, 11:56 AM
  5. run a half dozen reports from 1 click
    By techexpressinc in forum Reports
    Replies: 2
    Last Post: 12-17-2009, 11:20 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