Hi:
I need some assistance with tweaking existing VBA that would allow me to identify all fields that have *NO VALUES* in their associated tables.
Please see existing DB which does allow me to display only fields that *are populated* (again, I now need the inverse).
To better understand the process, do the following:
- Upon opening the DB, form "F02_ProductTables_AnalyzeData" opens
- Select either one of the 2 tables from the listbox
- Select either one of the 2 options from the radio button group
- Then click "Analyze Populated Fields"
Doing so will open up a query that only shows fields that contain at least one (1) value for any of the 10 records (for either table).
Now, at this point, I need to create the inverse (i.e., identify fields that do NOT contain a value).
Here's what I've done:
- Added 2nd query "02_qry_ProductTables_NonPopulatedFields"
- Added 2nd sub in module and named it "NonPopulatedFields()"
- In the For Each loop, I then removed the two (2) instance of "NOT" [if necessary, cross-reference same code in the PopulatedFields() sub.]
Existing Dilemma:
- Upon selecting command button "Analyze Non-Populated Fields", nothing happens!
- When attempting to open query "02_qry_ProductTables_NonPopulatedFields", nothing happens either.
Potential-Workaround:
- Naturally, I do not need to analyze the 10 records for which I don't have data.
- However, what I do need is a simple data output (e.g., single field where records = field names that are non-populated).
- For instance, when clicking on "Analyze Non-Populated Fields" for [tbl_INJURY], it would be great to have a query pop-up that includes six (6) records such as:
1. INJ_ENTRY_DATE
2. INJ_INJURY_MECHANISM_TIER3_ID
3. INJ_INJURY_TYPE_TIER2_ID
4. INJ_COMPLETED_RELIEF_DATE
5. INJ_COMPLETED_RELIEF_TIME
6. INJ_COMPRESSION_START_DATE
How should be VBA be tweaked to accomplish the output of the a) 6 records with empty fields for table [tbl_INJURY] and b) 4 records with empty field names for [tbl_MISHAP]?
Thank you,
EEH