Count selection of fields with value per record 1

[ragnarok75]

I have a table with the following fields

Autonumber
F1
F2
F3
F4
Q1
Q2
Q3
Q4
Q5
Q6
Q7

I need to write code which will count the number of F fields that are not null per each record. I want the same done for Q fields - Count of F and Count of Q.

Any ideas?

 

[CautionMP]

ragnarok75,
If you are using ADODB recordsets you could do something like the following. Since you didn't specify what to do with the counts I just output them to the immediate window.

Sub CountNotNullByGroup()
Dim rstMyRecordset As New ADODB.Recordset
Dim fldMyField As ADODB.Field
Dim intF As Integer, intQ As Integer
Dim sqlMyRecordset As String

sqlMyRecordset = "SELECT * FROM [b][i]YourTableNameHere[/i][/b];"
rstMyRecordset.Open sqlMyRecordset, CurrentProject.Connection
Do
  For Each fldMyField In rstMyRecordset.Fields
    If Not IsNull(fldMyField.Value) Then
      If Left(fldMyField.Name, 1) = "F" Then
        intF = intF + 1
      ElseIf Left(fldMyField.Name, 1) = "Q" Then
        intQ = intQ + 1
      End If
    End If
  Next fldMyField
  Debug.Print rstMyRecordset.Fields(0), "F count: " & intF, "Q count: " & intQ
  rstMyRecordset.MoveNext
  intF = 0
  intQ = 0
Loop Until rstMyRecordset.EOF
rstMyRecordset.Close
Set rstMyRecordset = Nothing
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[formerTexan]

You can also consider opening a recordset based on an aggregate query to return your counts if performance becomes an issue.

SELECT Count(Table1.MyField) AS CountOfMyField
FROM Table1
HAVING ((Left([MyField],1)="Q" And Not (Count(Table1.MyField)) Is Null));

Cheers,
Bill

 

[ragnarok75]

I tried Caution's suggestion. It worked, however I am not sure which record it was counting. I modified the code - the sqlMyRecordset I added a where statment which pulled the autonumber from the form. I anticipated that it would then count only the current record loaded on the form, however it counted all values regardless of whether or not they had value. I wanted the counts output to a text field on the form which worked, the count was just not right.

Dim fldNo As Integer

fldNo = txtID.Value

Dim rstMyRecordset As New ADODB.Recordset
Dim fldMyField As ADODB.Field
Dim intF As Integer, intQ As Integer
Dim sqlMyRecordset As String

sqlMyRecordset = "SELECT * FROM Table where Table.id = " & fldNo
rstMyRecordset.Open sqlMyRecordset, CurrentProject.Connection
Do
  For Each fldMyField In rstMyRecordset.Fields
    If Not IsNull(fldMyField.Value) Then
      If Left(fldMyField.Name, 1) = "F" Then
        intF = intF + 1
      ElseIf Left(fldMyField.Name, 1) = "Q" Then
        intQ = intQ + 1
      End If
    End If
  Next fldMyField
  CntPU.Value = intF
  CntDv.Value = intQ
'Debug.Print rstMyRecordset.Fields(0), "F count: " & intF, "Q count: " & intQ
  rstMyRecordset.MoveNext
  intF = 0
  intQ = 0
Loop Until rstMyRecordset.EOF
rstMyRecordset.Close
Set rstMyRecordset = Nothing

As for formerTexan's idea, I like the idea of having a query evaluate it, but I am not sure how to go about this.

 

[formerTexan]

I'm not sure what part you may be uncertain about, but to create and test the SQL strings, open up a QBE (query) window and create a query there that does what you want. Once it is returning the records you want change to SQL view and copy/paste the SQL string into your VBA module. From there you can make any final modifications to the SQL string that might be needed.

Cheers,
Bill

 

[CautionMP]

ragnarok75,
I originally wondered how you were going to use this so the initial routine processed everything. Since you are only looking for the stats from one record we can filter by that record and take the [tt]Do...Loop Until...EOF[/tt] out.

Here is an updated routine that accounts for the filter and provides a mechanism to get the two values out (I also threw in an Error handler).

Sub CountNotNullByGroup2(RecordId As Integer, ByRef F_Output As Variant, ByRef Q_Output As Variant)
On Error GoTo Error_Handler
Dim rstMyRecordset As New ADODB.Recordset
Dim fldMyField As ADODB.Field
Dim intF As Integer, intQ As Integer
Dim sqlMyRecordset As String

sqlMyRecordset = "SELECT * FROM Table WHERE ID=" & RecordId & ";"
rstMyRecordset.Open sqlMyRecordset, CurrentProject.Connection

For Each fldMyField In rstMyRecordset.Fields
  If Not IsNull(fldMyField.Value) Then
    If Left(fldMyField.Name, 1) = "F" Then
      intF = intF + 1
    ElseIf Left(fldMyField.Name, 1) = "Q" Then
      intQ = intQ + 1
    End If
  End If
Next fldMyField

CleanUp:
rstMyRecordset.Close
Set rstMyRecordset = Nothing
F_Output.Value = intF
Q_Output.Value = intQ
Exit Sub

Error_Handler:
Select Case Err.Number
  Case 3021
    'There was no record in rstMyRecordset
    intF = -1
    intQ = -1
    GoTo CleanUp
  Case Else
    Debug.Print Err.Number, Err.Description
End Select
End Sub

I tested this on a form with three text boxes and one Command button with the following code:

Private Sub Command1_Click()
CountNotNullByGroup2 Me.fldNo, Me.CntPU, Me.CntDv
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)