I have a SQL Server database that I'm writing a front end for in Access 2003.
I need to check to be sure certain fields have data. It's about 60
different fields. What's the most efficient way to do this?

Do you need to check all 60 fields?
Can the fields contain zero-length strings or will they be null if they have
no data?
Do you need to know which fields have no data in the record or just that some
field in a record has no data?
Are you checking just the current record that is being entered on a form?




John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Sash wrote:
> I have a SQL Server database that I'm writing a front end for in Access 2003.
> I need to check to be sure certain fields have data. It's about 60
> different fields. What's the most efficient way to do this?

Yes I'd like to check all 60 fields. The fields will be null if they have no
data. I had planned to open a recordset and loop through on the close of a
form.

The table contains fields required for an upload into a materials management
system and I'm having the users key into form and then process the file in
the correct format to be auto-loaded.

"John Spencer" wrote:

> Do you need to check all 60 fields?
> Can the fields contain zero-length strings or will they be null if they have
> no data?
> Do you need to know which fields have no data in the record or just that some
> field in a record has no data?
> Are you checking just the current record that is being entered on a form?
>
>
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Sash wrote:
> > I have a SQL Server database that I'm writing a front end for in Access 2003.
> > I need to check to be sure certain fields have data. It's about 60
> > different fields. What's the most efficient way to do this?
>

One way would be to concatenate the fields together and then check to
see if you have a null result.

SELECT *
FROM YourTable
WHERE A + B + C + D + E is Null
OR F + G + H + I + J is Null
OR AA + BB + CC + DD + EE + FF + GG + HH + II is Null
OR ...

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Sash wrote:
> Yes I'd like to check all 60 fields. The fields will be null if they have no
> data. I had planned to open a recordset and loop through on the close of a
> form.
>
> The table contains fields required for an upload into a materials management
> system and I'm having the users key into form and then process the file in
> the correct format to be auto-loaded.
>
> "John Spencer" wrote:
>
>> Do you need to check all 60 fields?
>> Can the fields contain zero-length strings or will they be null if they have
>> no data?
>> Do you need to know which fields have no data in the record or just that some
>> field in a record has no data?
>> Are you checking just the current record that is being entered on a form?
>>
>>
>>
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Sash wrote:
>>> I have a SQL Server database that I'm writing a front end for in Access 2003.
>>> I need to check to be sure certain fields have data. It's about 60
>>> different fields. What's the most efficient way to do this?