Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2018, 10:47 AM   #1
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Count Non-Null fields in table

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!

JJSHEP89 is offline   Reply With Quote
Old 11-09-2018, 11:17 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,340
Thanks: 0
Thanked 737 Times in 722 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Count Non-Null fields in table

you can make a query to do this ,instead of code.
Ranman256 is offline   Reply With Quote
Old 11-09-2018, 12:10 PM   #3
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Count Non-Null fields in table

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

JJSHEP89 is offline   Reply With Quote
Old 11-09-2018, 12:26 PM   #4
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Count Non-Null fields in table

Quote:
Originally Posted by Ranman256 View Post
you can make a query to do this ,instead of code.

I need it to execute on a button push, then print the reports. That's why I automatically went this route
JJSHEP89 is offline   Reply With Quote
Old 11-09-2018, 12:53 PM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,326
Thanks: 10
Thanked 2,264 Times in 2,216 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Count Non-Null fields in table

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
JJSHEP89 (11-12-2018)
Old 11-09-2018, 08:03 PM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,706
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Count Non-Null fields in table

Quote:
Originally Posted by JJSHEP89 View Post
.. 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 = dbs.OpenRecordset("SELECT inspectionset ...")
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
JJSHEP89 (11-12-2018)
Old 11-15-2018, 07:56 AM   #7
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 121
Thanks: 27
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: Count Non-Null fields in table

Quote:
Originally Posted by plog View Post
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.


JJSHEP89 is offline   Reply With Quote
Reply

Tags
count fields , count null , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count non null fields FrontSlider Modules & VBA 13 04-23-2012 05:56 AM
List fields from one table & count another? TheWedgie Reports 5 07-31-2010 04:05 AM
Count total fields in a record with null values nharrison Queries 2 06-16-2009 08:16 AM
<Null> of the fields in the table accessman2 SQL Server 2 04-20-2007 03:23 AM
count fields that have null values for one record??? supernintendo Queries 1 05-03-2003 01:20 PM




All times are GMT -8. The time now is 12:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World