Creating loop to run functions for each variable in database

[Imran J Khan]

I have a simple flat file database of a questionaire of about 130
questions/variablea. I need to a report with the frequencies, mean, median,
mode, min, max, range, standard deviation etc. I thought it would be easier
if I could code VBA to create functions for each of the above (frequencies,
mean, median etc) and that would go through each variable and calculate the
each function for that variable. I need help creating a loop that will go
through each variable and pass the nescessary arguments for each function
etc. I am a novice vba programmer, so I may not be very clear in
explaining what I want.

 

[Klatuu]

A loop wont work using individual variables, but it would work with either a
recordset record or an array.

How are these variables definded and populated?

 

[Imran J Khan]

By variables i meant fields in the database. They are populated using a data
entry form in the same Access Database. Whatever value is entered is what
that variable is defined as . Hope this is a little more clear. And also,
Thanks for the reply, I really appreciate your effort.

 

[Klatuu]

Okay, you can loop through your table as a recordset. Then you can loop
through the Fields collection of each record. A record has a Fields
collection that can be indexed either by the field's name or by its ordinal
number. The numbering starts at 0, so the first field in the record is
Fields(0), the last is Fields(Fields.Count -1)

So, hopefully, the fields are all contiguous in the record. Here is a
sample of how you might do this:

Dim rst As DAO.Recordset
Dim lngFldCnt As Long

Set rst = Currentdb.OpenRecordset("MyTableName")
If rst.RecordCount = 0 Then
MsgBox "No Data Available"
Exit Sub
End If

'Fully populate the recordset

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
'Evaluate the fields in the record starting with the fourth field.
For lngFldCnt = 3 To rst.Fields.Count -1
'Do your functions here.
Next lngFldCnt
.MoveNext
Loop
.Close
End With
Set rst = Nothing

 

[Imran J Khan]

Fantastic Dave, Thanks a lot. I can't try it right away as I still have to
wait for the data to be entered. I will add my functions where you indicated
in the mean time. Thanks again.