This should help
Code:Sub yearAvg() 'some test data 6 records Dim y(5) As Integer y(0) = 1960 y(1) = 1971 y(2) = 0 y(3) = 1971 y(4) = 0 y(5) = 1960 ' A valid year is a year value that is not blank/empty/0 Dim i As Integer Dim ValidYearTot As Long 'Sum of Valid Year values Dim ValidYearCnt As Integer 'Count of Valid Years For i = 0 To 5 'If the year is NOT blank/empty/0, then include it in calculation If y(i) > 0 Then ValidYearCnt = ValidYearCnt + 1 ValidYearTot = ValidYearTot + y(i) Else 'Year is blank/empty/0 so ignore it End If Next i 'determine the avg using only valid years ' Sum of Valid years divided by count of Valid years MsgBox "Avg Year (excluding blanks) is " & ValidYearTot / ValidYearCnt End Sub
Last edited by orange; 05-13-2012 at 09:32 AM. Reason: typo
I think you can do this with a query. Something like:
Replace TblName with your table Name.Code:SELECT TblName.[Legal_1], Sum(TblName.[Year_Built]) AS YrTot, Count(TblName.[Year_Built]) AS YrTot, [TotNum]/[YrTot] AS Result FROM TblName WHERE (((TblName.[Year_Built])>0)) GROUP BY TblName.[Legal_1];
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Pardon my ignorance, but where do you enter in the custom query code you have written? I am only familiar with simple queries such as query wizard and query by design.
Thank you very much for writing the code for this specific problem, I appreciate it.
The code is untested, so it may not workThank you very much for writing the code for this specific problem, I appreciate it.
Open a new query in design view. Change the view to SQL view. Cut and paste the supplied code. Remember to change the table/query name that is red.
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Hi -
This is easily done with a totals query, in query design view.
1) Add the two required fields to your query.
2) Make the query into a Totals query. A New Totals row will be shown, with both fields initially set to "Group By"
3) For the Year_Built field, change Group By to Avg (select from dropdown)
4) In the Criteria field for Year_Built, enter >0 .
That should do it for you - try running the query.
HTH
John