**Dynamically* include fields (in a query) where record count <> NULL

[skydivetom]

Jack -- thank you... I appreciate the feedback. I'll have another look at your latest version.



I'd welcome if you also could see my updates (post #14). I took your code and merely added an IF statement (for mid portion of SQL) to determine whether it's "Details" or "Summary Count". I also added the closing routines (of the query) when switching between table values and/or option groups.

Again, I'm always for streamlining code... sounds like both version do the "trick". Thanks again to you (as well as kd2017) for the fantastic help!

Cheers,
Tom

[davegri]

If you want to eliminate the primary key column from the created query, since it will never be null, you can use this function to get its name:

Function PrimaryKey(strTableName As String) As String
    Dim td As DAO.TableDef
    Dim db As DAO.Database: Set db = CurrentDb
    Dim idx As Index
    Set td = db.TableDefs(strTableName)
    For Each idx In td.Indexes
    If idx.Primary = True Then
        PrimaryKey = Mid(idx.Fields, 2)
        Exit For
    End If
    Next idx
    Set db = Nothing
    Set td = Nothing
End Function

[skydivetom]

davegri -- thank you for chiming in... interesting thought. In fact, I thought about it this morning when making coffee but didn't go back to implement the removal of the AUTONUMBER field.

Please see attached the latest/current version (little bit cleaner)... this one also contains the "sort fields within table" function (courtesy by Vlad C.).

That said, how would your code have to be implemented into the existing procedure to remove the ID field?

Cheers,
Tom

[davegri]

how would your code have to be implemented into the existing procedure to remove the ID field?

Add the function code to module modViewPopulateFields

Then modify the sub PopulatedFields:

          .
          .
          .
    Dim sresult As String
    Dim QString As String
    Dim i As Integer
    Dim FieldsSQL
    Dim PKName As String
    Dim createSQL1 As String    'Start SQL
    Dim createSQL2 As String    'End SQL
        
    Dim sTableName As String
    sTableName = Forms!F02_ProductTables_AnalyzeData!lstSourceFile
    PKName = PrimaryKey(sTableName)
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sTableName)
    
    For Each fld In rs.Fields
        If fld.Name <> PKName Then
            If fld.Value > " " Then
                sresult = sresult & fld.Name & " "
            End If
        End If
    Next
    sresult = Mid(sresult, 1, Len(sresult) - 1)
    FieldsSQL = Split(sresult, " ")
          .
          .
          .

[skydivetom]

davegri -- I integrated your change recommendations. Although the function executes, the queries now don't output what I anticipate. See attached JPG for details.

1. For testing only, I removed values for 2 entire records (#4, #21) in tbl_Example_1; I did NOT remove all values across one record in tbl_Example_2.
2. Now, when executing the query (Details view) for tbl_Example_1, 2 rows are completely "empty". However, the record count remains to be 50 (vs. 48).
3. Performing the same (details for tbl_Example_2), show me one record without any values (see highlighted in yellow in JPG). But again, there's no entire NULL record for the 2nd table.
4. Also, in review of tbl_Example_2, I have values in fields: 01, 03, 05, 07, 10, 13, 15, 17, 20. When executing the "Count" (table 2), however, it only shows fields 01, 03, 10, 13, 15, and 20.

I don't think I noticed those "oddities" in the previous version, but I'll double-check to verify. In the meanwhile, is there may something missing in the adjusted code that would explain these results? Again, if this was also the case for the previous version, what am I missing in the code that would explain fields 05, 07, 17 to drop from the view (for table 2)?

Thanks,
Tom

[davegri]

Dynamic Query-davegri v03.zip

I don't get those blank lines with the code I added to your DB from post#18.
See attached.

[skydivetom]

Davegri, Jack:

Quick follow-up... please see attached JPGs. Please note that FIELD_05, FIELD_07 have values (e.g., "345" for record #8 for FIELD_05 and "955" starting @ record #7.) However, both of the 2 fields are NOT included in the Details' view (see view on the right in the snapshot).

Here's my hunch... are they not included because they have NULL values in the 1st record? If so, how could the code be tweaked so that these 2 columns (and any other are included in the details count)?

Thank you,
Tom

[davegri]

OK. Generated queries need a WHERE clause to look for IS NOT NULL across the rows.

This version should fix it.
Also fixes blank rows showing.
Omits PK column.

Dynamic Query-davegri v04.zip

Here's a generated query with new WHERE clause:

SELECT FIELD_01, FIELD_03, FIELD_05, FIELD_07, FIELD_10, FIELD_13, FIELD_15, FIELD_17, FIELD_20
FROM tbl_Example_2
WHERE FIELD_01 IS NOT NULL  OR FIELD_03 IS NOT NULL  OR FIELD_05 IS NOT NULL  OR FIELD_07 IS NOT NULL  OR FIELD_10 IS NOT NULL  OR FIELD_13 IS NOT NULL  OR FIELD_15 IS NOT NULL  OR FIELD_17 IS NOT NULL  OR FIELD_20 IS NOT NULL;

[skydivetom]

Davegri -- this is totally awesome!

Yes, I now get the fields with contain a NULL value in the 1st record... awesome!
And, yes, with the PK omission in place, I now get fewer than 50 records -- for either table -- given I have some blank records (for testing only).

So again, thanks to Jack for providing the framework of this code. And thank you, Davegri, for helping me resolve the removal of the pk column and the glitch with the missing fields.

Cheers,
Tom