1. Registered User
Join Date
Nov 2010
Location
New Jersey
Posts
18

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.

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. Moderator
Join Date
Mar 2009
Posts
5,442
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

3. Registered User
Join Date
Nov 2010
Location
New Jersey
Posts
18
Originally Posted by Sinndho
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. Moderator
Join Date
Mar 2009
Posts
5,442
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 ...```

5. Moderator
Join Date
Mar 2009
Posts
5,442
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.

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

File is not an access file, will not open?

7. Moderator
Join Date
Mar 2009
Posts
5,442
It's in the new accdb file format, the same format that the original file you sent.

8. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442