Show records that are incomplete

[chartleb]

Hi,

I have a form with several text boxes that are used to filter the results that are displayed in a list box. Everything works fine but I would also like to display incomeplete records. For instance I would like to display records that only have three of the fields rather than all of the fields.

Here is my current query:

[SIZE=2]SELECT [tblDrop].[Drop ID], [tblDrop].[Drop Number], [tblDrop].[Type], [tblDrop].[Color], [tblDestination].[Switch ID], [tblDestination].[Switch Port], [tblDestination].[Phone Patch ID], [tblDestination].[Phone Patch Number] FROM tblDrop INNER JOIN tblDestination ON (tblDrop.[Drop ID] = tblDestination.[Drop ID]) AND (tblDrop.[Drop Number] = tblDestination.[Drop Number]) WHERE ((([tblDrop].[Location ID]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchLocation] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchLocation] Is Null)) AND ((([tblDrop].[Drop Number]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchDropNumber] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchDropNumber] Is Null)) AND ((([tblDrop].[Drop ID]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchDropID] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchDropID] Is Null)) AND ((([tblDrop].[Type]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchType] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchType] Is Null)) AND ((([tblDrop].[Color]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchColor] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchColor] Is Null)) AND ((([tblDestination].[Switch ID]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchSwitch] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchSwitch] Is Null)) AND ((([tblDestination].[Switch Port]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPort] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPort] Is Null)) AND ((([tblDestination].[Phone Patch ID]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPhone] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPhone] Is Null)) AND ((([tblDestination].[Phone Patch Number]) Like [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPatch] & '*' OR [forms]![frmNavigation]![NavigationSubform].Form.[txtSearchPatch] Is Null)) ORDER BY [tblDrop].[Drop Number];[/SIZE]

Thanks for looking!

 

[Beetle]

Use some VBA code (behind a command button, for example) to create the query and build the WHERE clause based on the values that were entered in any of the text boxes. Then apply the results to the Row Source of your list box. In the following example I have assumed that your fields are text data type since you are using the Like operator so I have used string delimiters in the code. This example only includes code for three text boxes, but you should be able to follow the logic and add more lines for additional search boxes.

Dim strSQL As String, strWhere As String
Dim lngLength As Long
'Build the SQL for the query
strSQL = "SELECT tblDrop.[Drop ID], tblDrop.[Drop Number], tblDrop.[Type]," _
       & " tblDrop.[Color], tblDestination.[Switch ID], tblDestination.[Switch Port]," _
       & " tblDestination.[Phone Patch ID], tblDestination.[Phone Patch Number]" _
       & " FROM tblDrop INNER JOIN tblDestination ON tblDrop.[Drop ID] =" _
       & " tblDestination.[Drop ID] AND tblDrop.[Drop Number] = tblDestination.[Drop Number]"
       
'Check each text box for a value and build the criteria string
If Nz(Me!txtSearchLocation, "") <> "" Then
    strWhere = strWhere & "[Location ID] Like """ & Me!txtSearchLocation & "*"" And "
End If
If Nz(Me!txtSearchDropNumber, "") <> "" Then
    strWhere = strWhere & "[Drop Number] Like """ & Me!txtSearchDropNumber & "*"" And "
End If
If Nz(Me!txtSearchDropID, "") <> "" Then
    strWhere = strWhere & "[Drop ID] Like """ & Me!txtSearchDropID & "*"" And "
End If
If Len(strWhere) > 0 Then 'Something was entered
    'Remove the trailing " And "
    lngLength = Len(strWhere) - 5
    strWhere = Left(strWhere, lngLength)
    
    'Complete the WHERE clause and add it to strSQL
    strWhere = " WHERE " & strWhere
    strSQL = strSQL & strWhere
    
    'Set the list box Row Source
    Me!lstYourListBox.RowSource = strSQL
    
Else 'Nothing was entered so display a message box
    MsgBox "You did not enter any criteria."
    
End If

BTW - Type is a Reserved word in Access, so you should not use it as a field name. For more on Reserved words see;

http://allenbrowne.com/AppIssueBadWord.html#T