Query to list all blank fields for one record

[mundo7]

Hi,

I have a big table which lists all of my customers, and their associated details (address, phone number, etc).

I want to produce a report for any particular customer (chosen by combobox on form) which lists the fields which are blank in the following format:

XXX Co Ltd
Address Line 2
Postcode
Fax Number
Website Address

Is there a way of doing this?

Cheers

Mundo

 

[DCrake]

Create a query containing the fields to test for null and use Is Null in the conditions (not on the same line step them down)

Field1_______Field 2

Is Null
___________Is Null

This will give you an Or condition not an And

David

 

[mundo7]

Hi,

Thanks for that,

however there are 30+ fields to list.

Is it possible to only show the fields that are blank? currently it is bringing back all fields i am checking, which kind of defeats the purpose of checking (i.e. i may as well bring back the whole record)

 

[DCrake]

Ok try this

On the Afterupdate of your combobox call the following function

Dim TmpStr as string
TmpStr = NullFields(Me.ComboBox) 'Customer ID

Function NullFields(AnyCustomer As Long) As String
Dim strString As String

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * from tblCustomers Where fldFileID =" & AnyCustomer)

If Not Rs.EOF And Not Rs.BOF Then
    For x = 0 To Rs.Fields.Count - 1
        If IsNull(Rs(x)) Then
            strString = strString & Rs(x).Name & vbNewLine
        End If
    Next
    Rs.Close
End If

Debug.Print strString
Set Rs = Nothing
NullFields = strString
End Function

If Trim(TmpStr) <> "" then
   MsgBox "The following fields have null values:" & vbcrlf & vbcrlf & TmpStr, VbInformation+vbOkOnly,"Customer Id " & Me.ComboBox
Else
   Msgbox "No null fields found",vbInformation,vbOkOnly,"Customer ID " & Me.ComboBox
End If

This is just an illustration using message boxes, however when using it in a report you bind a textbox to the function in the default value/control source.

David