DAO IgnoreNulls and copy value down a table

[tcarter963]

I've got some data that I'm trying to dump into a database from excel. Everything is fine except I'm stuck on trying to get the average of some values into the database. Because sometimes I've got 2,3,4, or 5 values that I'm taking the average of and getting some statistical data on, I have blanks between my calculations as it fills down a column. I found this IgnoreNulls property but I'm not sure how to create a new index, as is suggested in the excel help example.

There is one value in this table that isn't an average, it's actually a single value, the Batch#. I would like for this value to copy down for each of my average values in the access table.

Below is part of the code as it is now. I don't have to do anything special to all the other fields/columns that I'm exporting. Any help, or just a hint on the right direction to take would be greatly appreciated.



' Average of Results

Set rsa = db.OpenRecordset("ResultsAve", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("S" & r).Formula) > 0
' repeat until first empty cell in column S, which is the individual results ID field


With rsa
.AddNew ' create a new record
' add values to each field in the record
.Fields("Batch#") = Range("B" & r).Value
.Fields("Time_days") = Range("X" & r).Value
.Fields("ReleaseAve_µg") = Range("Y" & r).Value
.Fields("ReleaseStdev") = Range("Z" & r).Value
.Fields("µg_day") = Range("AA" & r).Value
.Fields("Stdev_µg_day") = Range("AB" & r).Value
.Fields("C_Ave_µg") = Range("AC" & r).Value
.Fields("C_Ave%") = Range("AD" & r).Value
.Fields("C_Stdev%") = Range("AE" & r).Value
.Fields("COV") = Range("AF" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rsa.Close
Set rsa = Nothing

 

[tcarter963]

I'm pretty sure that you can't do this with DAO. The work around that I did was to copy and paste data in the format that I wanted it in, then transfer via DAO, and delete the formatted data that I no longer needed.