Count Non-Null fields in table (1 Viewer)

JJSHEP89

Registered User.
Local time
Today, 10:35
Joined
Aug 18, 2016
Messages
121
I have a table within my DB that contains quality verification limits for specifi parts for a manufacturing facility. There are individual fields for upper and lower size limits for inspections A through Z plus a few other standard checks (~60 fields total). This fields are used to populate 3 reports, each similarly formatted, with the upper and lower limits shown for A-Z and printed by the user to be filled out during the manufacturing process (A-K on report1, L-Q on report2, R-S report3). The user prints these forms based on a series of selection criteria based on the part and the manufacturing operation in a separate Pop-Up form.

Now to the issue...
Not all fields are used for each part number, some may only need one inspection and I don't want to print all 3 reports if only the first is needed. What I'm thinking would be the best method is to simply loop through each field and count the number of Non-Null values, then a select case statement to evaluate the count and determine which reports need to be printed. But I'm not sure the code I would need to loop through each field within a specific range... can anyone help? I'm familiar with Loops just not with looking through a list of fields without writing If Then statements for each one (which i'd rather not do for 120 fields)

Im thinking something like this (not tested yet)
Code:
Private Sub btnPrintJobPackageComplete_Click()
Dim rst As DAO.Recordset
Dim fld As Field
Dim InspCount as Int

Set rst = "SELECT ..."     'SQL for all ~120 relevant fields

While Not rst.EOF
   For Each .....
      If Not IsNull(rst(fld).Value Then
         InspCount = InspCount + 1
      End If
   rst.MoveNext
Wend
Am I on the right track here?

Additionally, and this is something that would be cool to do but not necessary, is to have each column on the reports automatically size based on the number of used fields. For example, if one part only requires inspections A-C, I don't need to take up the extra space on the printed sheet with columns D-K. So i would like to resize the columns to maximize their width on the page, increasing the area for the user to write in his measurements.

Any suggestions or links to other similar threads (search didn't return an adequate answer) would be greatly appreciated.

Thanks in advance!
 

Ranman256

Well-known member
Local time
Today, 11:35
Joined
Apr 9, 2015
Messages
4,339
you can make a query to do this ,instead of code.
 

JJSHEP89

Registered User.
Local time
Today, 10:35
Joined
Aug 18, 2016
Messages
121
This is what I've created thus far... doesn't seem to be working for me, I keep getting a "424 - Object required" error coming from my SQL statement...

Code:
Private Sub btnPrintJobPackageComplete_Click()
Dim strFormName, strResource As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As Field
Dim InspCount As Integer

On Error GoTo ErrorHandler:

Set dbs = CurrentDb()
Set rst = "SELECT inspectionsetup.A_MeasureInstOnline, inspectionsetup.A_UpperLimit, inspectionsetup.A_LowerLimit, inspectionsetup.B_MeasureInstOnline, inspectionsetup.B_UpperLimit, inspectionsetup.B_LowerLimit, " & _
            "inspectionsetup.C_MeasureInstOnline, inspectionsetup.C_UpperLimit, inspectionsetup.C_LowerLimit, inspectionsetup.D_MeasureInstOnline, inspectionsetup.D_UpperLimit, inspectionsetup.D_LowerLimit, " & _
            "inspectionsetup.E_MeasureInstOnline, inspectionsetup.E_UpperLimit, inspectionsetup.E_LowerLimit, inspectionsetup.F_MeasureInstOnline, inspectionsetup.F_UpperLimit, inspectionsetup.F_LowerLimit, " & _
            "inspectionsetup.G_MeasureInstOnline, inspectionsetup.G_UpperLimit, inspectionsetup.G_LowerLimit, inspectionsetup.H_MeasureInstOnline, inspectionsetup.H_UpperLimit, inspectionsetup.H_LowerLimit, " & _
            "inspectionsetup.I_MeasureInstOnline, inspectionsetup.I_UpperLimit, inspectionsetup.I_LowerLimit, inspectionsetup.J_MeasureInstOnline, inspectionsetup.J_UpperLimit, inspectionsetup.J_LowerLimit, " & _
            "inspectionsetup.K_MeasureInstOnline, inspectionsetup.K_UpperLimit, inspectionsetup.K_LowerLimit, inspectionsetup.L_MeasureInstOnline, inspectionsetup.L_UpperLimit, inspectionsetup.L_LowerLimit, " & _
            "inspectionsetup.M_MeasureInstOnline, inspectionsetup.M_UpperLimit, inspectionsetup.M_LowerLimit, inspectionsetup.N_MeasureInstOnline, inspectionsetup.N_UpperLimit, inspectionsetup.N_LowerLimit, " & _
            "inspectionsetup.O_MeasureInstOnline, inspectionsetup.O_UpperLimit, inspectionsetup.O_LowerLimit, inspectionsetup.P_MeasureInstOnline, inspectionsetup.P_UpperLimit, inspectionsetup.P_LowerLimit, " & _
            "inspectionsetup.Q_MeasureInstOnline, inspectionsetup.Q_UpperLimit, inspectionsetup.Q_LowerLimit, inspectionsetup.R_MeasureInstOnline, inspectionsetup.R_UpperLimit, inspectionsetup.R_LowerLimit, " & _
            "inspectionsetup.S_MeasureInstOnline, inspectionsetup.S_UpperLimit, inspectionsetup.S_LowerLimit, inspectionsetup.T_MeasureInstOnline, inspectionsetup.T_UpperLimit, inspectionsetup.T_LowerLimit, " & _
            "inspectionsetup.U_MeasureInstOnline, inspectionsetup.U_UpperLimit, inspectionsetup.U_LowerLimit, inspectionsetup.V_MeasureInstOnline, inspectionsetup.V_UpperLimit, inspectionsetup.V_LowerLimit, " & _
            "inspectionsetup.W_MeasureInstOnline, inspectionsetup.W_UpperLimit, inspectionsetup.W_LowerLimit, inspectionsetup.X_MeasureInstOnline, inspectionsetup.X_UpperLimit, inspectionsetup.X_LowerLimit, " & _
            "inspectionsetup.Y_MeasureInstOnline, inspectionsetup.Y_UpperLimit, inspectionsetup.Y_LowerLimit, inspectionsetup.Z_MeasureInstOnline, inspectionsetup.Z_UpperLimit, inspectionsetup.Z_LowerLimit, " & _
            "inspectionsetup.AA_MeasureInstOnline, inspectionsetup.AA_MeasureInstDetail, inspectionsetup.AA_UpperLimit, inspectionsetup.AA_LowerLimit " & _
            "FROM inspectionsetup " & _
            "WHERE  (((inspectionsetup.PartNumber)= '" & Me.cboPartNumber & "') AND ((inspectionsetup.DieNum)= '" & Me.cboDieNumber & "') AND ((inspectionsetup.OpSeq)= '" & Me.cboOperationSequence & "'));"

    While Not rst.EOF
        For Each fld In rst
            If Not IsNull(rst(fld).Value) Then
                InspCount = InspCount + 1
            End If
            Next fld
        rst.MoveNext
    Wend
    rst.Close

MsgBox "the total Number of Non-Null fields is '" & InspCount & "'"

    Exit Sub
ErrorHandler:
        MsgBox Err.Number & " - " & Err.Description & vbNewLine & "If the error persists, close and re-open the Info Center.", , "Info Center Database Error"
        Exit Sub
 

plog

Banishment Pending
Local time
Today, 10:35
Joined
May 11, 2011
Messages
11,611
Ugh. You need to fix your table. Do that and this becomes trivial.

You are not working with a database, but a spreadsheet living inside Access. You need to properly normalize that data and then you can run a simple query to do this instead of hacking together code to overcome it.

Tables should acomodate data vertically (with more rows), not horizontally (with more clumns. You should reduce the number of fields by a factor of at least 26. The letter identifier should come out of your field name and be put into a field value (if it's even needed.

You have 108 fields in sets of 4 like so:

AA_MeasureInstOnline, AA_MeasureInstDetail, AA_UpperLimit, AA_LowerLimit

Instead you should have a new table which has 5 fields to accomodate all of that data:

LetterValue, MeasureInstOnine, MeasureInstDetail, UpperLimit, LowerLimit

Then to put in 27 letters worth of data, you make 27 records in this new table described above. Fix your tables like that and this hack you are working on goes away.
 

JHB

Have been here a while
Local time
Today, 16:35
Joined
Jun 17, 2012
Messages
7,732
.. I keep getting a "424 - Object required" error coming from my SQL statement...
..
I have only taken a position on your problem with SQL string, the problem is because you're missing the dbs.OpenRecordset in the SQL statement.
You've:
Code:
Set rst = "SELECT inspectionset ...
Correct is:
Code:
Set rst = [B][COLOR=Red]dbs.OpenRecordset([/COLOR][/B]"SELECT inspectionset ..."[B][COLOR=red])[/COLOR][/B]
 

JJSHEP89

Registered User.
Local time
Today, 10:35
Joined
Aug 18, 2016
Messages
121
Ugh. You need to fix your table. Do that and this becomes trivial.

You are not working with a database, but a spreadsheet living inside Access. You need to properly normalize that data and then you can run a simple query to do this instead of hacking together code to overcome it.

Tables should acomodate data vertically (with more rows), not horizontally (with more clumns. You should reduce the number of fields by a factor of at least 26. The letter identifier should come out of your field name and be put into a field value (if it's even needed.

You have 108 fields in sets of 4 like so:

AA_MeasureInstOnline, AA_MeasureInstDetail, AA_UpperLimit, AA_LowerLimit

Instead you should have a new table which has 5 fields to accomodate all of that data:

LetterValue, MeasureInstOnine, MeasureInstDetail, UpperLimit, LowerLimit

Then to put in 27 letters worth of data, you make 27 records in this new table described above. Fix your tables like that and this hack you are working on goes away.

Agreed, this is definitely the way to go about it. We've recently moved all of this data from a spreadsheet into our primary DB (developmental) and were trying to get a working prototype out to our users quickly. Essentially we were trying to force something into working in its current condition rather than fixing it and building it the way it needs to be done. Unfortunately our spreadsheet based system has reached catastrophic failure and we need to issue a new release using what we currently have but we will be restructuring this system to correct this issue. I appreciate all your suggestions.
 

Users who are viewing this thread

Top Bottom