[friendly]

I have a large database in access with one field having repeated values. I used a Select Distinct statement to fill a listbox with one of each value in that field. Now I want to select one of these values and get the average of the data from another field for all of the records with this value and put it into a textbox. It seems to get the data and take the average, but I don't know how t get the final number into a textbox. The code below is what I have been using.

Private Sub Form_Load()

Set ashcmd = New ADODB.Command
ashcmd.ActiveConnection = ashconn
Set ashrs = New ADODB.Recordset
ashsources = "select distinct Source from Flyash"
ashcmd.CommandText = ashsources
ashrs.Open ashcmd

'Filling source listboxes with Ash sources in database.
lstash.Clear
While (Not ashrs.EOF)
lstash.AddItem ashrs("Source")
ashrs.MoveNext
Wend

End Sub


Private Sub lstash_Click()

Set ashsbcmd = New ADODB.Command
ashsbcmd.ActiveConnection = ashconn
Set ashsbrs = New ADODB.Recordset
ashsbrs.ActiveConnection = ashconn

ashsource = lstash.List(lstash.ListIndex)
meansb = "select Avg(Antimony) from Flyash where Source ='" + ashsource + "'"
ashsbcmd.CommandText = meansb
ashsbrs.Open ashsbcmd

This is where I want to put the average of the data into a textbox.


Any help would be greatly appreciated.

Thanks,

Michael

[friendly]

I changed the code to the following and have gotten it to work a little better.


Private Sub lstash_Click()

Set ashsbcmd = New ADODB.Command
ashsbcmd.ActiveConnection = ashconn
Set ashsbrs = New ADODB.Recordset
ashsbrs.ActiveConnection = ashconn

ashsource = lstash.List(lstash.ListIndex)
meansb = "select * from Flyash where Source ='" + ashsource + "'"
ashsbcmd.CommandText = meansb
ashsbrs.Open ashsbcmd

While (Not ashcontentrs.EOF)
sb = ashcontentrs("Antimony")
sumsb = sumsb + sb
numsb = numsb + 1
ashcontentrs.MoveNext
Wend
avgab = sumsb / numsb


This gives me the average for all values in that field. Is there an easier way. Also, if a record in a given is blank, how do I tell it to skip that one and continue with the next?

Thanks in advance for any help given

Michael