Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Location
    New Jersey
    Posts
    18

    Question Unanswered: Need percentage help!!!!!

    Being a Novice at Access I'm not even sure this is possible......here is my question;

    I have a form that stores sub-tasks % complete (10 of them) that are part of one major task % complete.

    The major task % is calculated from the 10 sub-task percentages....what I would like Access to do is ignore the sub-tasks that are not used.

    Not all major tasks will have 10 sub-tasks....but my calculation will only work if all ten spots have a percentage listed even if I enter 0% it throws off the average.

    Is it possible to ignore the unused fields?? Here is my calculation field

    =([Text100]+[Text102]+[Text104]+[Text106]+[Text108]+[Text110]+[Text112]+[Text114]+[Text116]+[Text118])/10

    http://i170.photobucket.com/albums/u...S/screen-2.jpg

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the text boxes contain 0 (zero) when they are not in use (not a very good thing!) you can use:
    Dim i as Integer
    For i = 100 to 118 Step 2
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Location
    New Jersey
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    If the text boxes contain 0 (zero) when they are not in use (not a very good thing!) you can use:
    Dim i as Integer
    For i = 100 to 118 Step 2
    Can you walk me through that??

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OOps! Let's try again.
    If the text boxes contain 0 (zero) when they are not in use (not a very good thing!) you can use:
    Code:
    Function InUseZ() As Long
    
        Dim i As Long
    
        For i = 100 To 118 Step 2
            If Me.Controls("Text" & CStr(i)).Value <> 0 Then InUse = InUse + 1
        Next i
    
    End Function
    If the text boxes contain Null when they are not in use (that's better!) you can use:
    Code:
    Function InUseN() As Long
    
        Dim i As Long
    
        For i = 100 To 118 Step 2
            If Not IsNull(Me.Controls("Text" & CStr(i)).Value) Then InUse = InUse + 1
        Next i
    
    End Function
    In both cases:
    Code:
    =([Text100]+[Text102]+[Text104]+[Text106]+[Text108]+[Text110]+[Text112]+[Text114]+[Text116]+[Text118])/InUseZ ' (case 1) or /InUseN (case 2)
    Don't forget to check for the case the function returns zero!
    Code:
    Dim Div as long
    Div = InUseZ    ' or InUseN
    If Div > 0 Then ...
    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    George,

    Here's your database back. To see the modification, open the form in creation mode then press Alt+F11 to open the VBA editor.
    Attached Files Attached Files
    Have a nice day!

  6. #6
    Join Date
    Nov 2010
    Location
    New Jersey
    Posts
    18
    Hey...

    File is not an access file, will not open?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's in the new accdb file format, the same format that the original file you sent.
    Have a nice day!

  8. #8
    Join Date
    Nov 2010
    Location
    New Jersey
    Posts
    18
    Here is what is unzipped see picture below, the file is not associated with any program

    http://i170.photobucket.com/albums/u...S/access-1.jpg

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Strange! When I download it from my previous message it works. Let's try again then.
    Attached Files Attached Files
    Have a nice day!

Posting Permissions

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