DAO IgnoreNulls and copy value down a table

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
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.
 

Forum statistics

Threads
1,136,328
Messages
5,675,132
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top