Find records that have data missing from mandatory fields

  • Thread starter Thread starter pmartin1960
  • Start date Start date
P

pmartin1960

Hi all

I am using DoCmd.TransferSpreadsheet to import data from Excel into
Access tables. I need to be able to run an SQL query that will find
the records (by ID) and any blank mandatory fields for that record.

Any suggestions of the actual SQL query required are appreciated.

Paul Martin
Melbourne, Australia
 
Tom

This is not what I'm after. I want the ID and a list of fields for
that ID where values are missing. Those values will always be Null or
empty strings. I was thinking of perhaps a field consisting of a bunch
of if statements eg, (IF FieldX is null, "FieldX", "") & (If FieldY is
null, "FieldY", "").

This might be a little convoluted (especially with about 20 fields that
may need testing for nulls), so was looking for something perhaps a
little more elegant.

Paul Martin
Melbourne, Australia
 
Hi all

I am using DoCmd.TransferSpreadsheet to import data from Excel into
Access tables. I need to be able to run an SQL query that will find
the records (by ID) and any blank mandatory fields for that record.

Any suggestions of the actual SQL query required are appreciated.
Click to expand...

A somewhat hairy UNION query would be possible:

SELECT ID, "ThisField" FROM yourtable
WHERE [ThisField] IS NULL
UNION ALL
SELECT ID, "ThatField" FROM yourtable
WHERE [ThatField] IS NULL
UNION ALL
....

<etc through all the required fields>

John W. Vinson[MVP]
 
You can try something like:

SELECT [IDField]
FROM [YourTable]
WHERE (Len(Trim([MandatoryField1] & "")) = 0)
OR (Len(Trim([MandatoryField2] & "")) = 0)
OR (Len(Trim([MandatoryField3] & "")) = 0)
....
 
Hi, Paul.

Perhaps this is closer to what you're looking for:

SELECT ID, IIF(Nz(Field1, "") = "", "Field1", NULL) AS Fld1,
IIF(Nz(Field2, "") = "", "Field2", NULL) AS Fld2,
IIF(Nz(Field3, "") = "", "Field3", NULL) AS Fld3
FROM MyTable;

.. . . where Field1, Field2, and Field3 are the names of mandatory fields,
and MyTable is the name of the table. This will display the name of the
field if it's blank or has a zero-length string in it. But if you have 20
mandatory fields, then it will be 21 columns across, some of them blank, so
you'll be doing some scrolling.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
 
Back
Top