Form Search Code Multiple Related Tables

[bymarce]

My database contains 4 tables. The Master table is a catalog of scientific
samples with the sample ID being the MLO field. The other three tables are
data, conditions, and comments. The MLO table has a one to many relationship
with the other tables. The data table has a 1 to 1 relationship with the
conditions and comments tables with referential integrety inforced. I made a
form to search the data that has unbound controls in the header for the
search criteria to be entered into. The detail of the form brings up all the
filtered records. I used example code from Allen Browne's Access Tips:
Search Criteria. When my form has no values filled in it should return all
records but 10 are missing. When I search for data run at over 300 deg C it
should return 588 records but it only returns 5. Is there something wrong
with my code or is the problem related to my table relationships? Thanks for
the help. Here's the code:

Private Sub cmdFilter_Click()


Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.FMLO) Then
strWhere = strWhere & "([MLO] = """ & Me.FMLO & """) AND "
End If

If Not IsNull(Me.FMLOL) Then
strWhere = strWhere & "([MLO] >= """ & Me.FMLOL & """) AND "
End If

If Not IsNull(Me.FMLOH) Then
strWhere = strWhere & "([MLO] <= """ & Me.FMLOH & """) AND "
End If

If Not IsNull(Me.Description) Then
strWhere = strWhere & "([Description] Like ""*" & Me.Description &
"*"") AND "
End If

If Not IsNull(Me.Description2) Then
strWhere = strWhere & "([Description] Like ""*" & Me.Description2 &
"*"") AND "
End If

If Not IsNull(Me.Description3) Then
strWhere = strWhere & "([Description] Like ""*" & Me.Description3 &
"*"") AND "
End If

If Not IsNull(Me.Program) Then
strWhere = strWhere & "([Program] Like ""*" & Me.Program & "*"") AND "
End If

If Not IsNull(Me.Manufacture) Then
strWhere = strWhere & "([Manufacture] Like ""*" & Me.Manufacture &
"*"") AND "
End If

If Not IsNull(Me.Specification) Then
strWhere = strWhere & "([Specification] Like ""*" & Me.Specification
& "*"") AND "
End If

If Not IsNull(Me.Text29) Then
strWhere = strWhere & "([OCDVis] >= " & Me.Text29 & ") AND "
End If

If Not IsNull(Me.Text33) Then
strWhere = strWhere & "([OCDVis] <= " & Me.Text33 & ") AND "
End If

If Not IsNull(Me.Text30) Then
strWhere = strWhere & "([AN] >= " & Me.Text30 & ") AND "
End If

If Not IsNull(Me.Text35) Then
strWhere = strWhere & "([AN] <= " & Me.Text35 & ") AND "
End If

If Not IsNull(Me.Text25) Then
strWhere = strWhere & "([OCV] >= " & Me.Text25 & ") AND "
End If

If Not IsNull(Me.Text37) Then
strWhere = strWhere & "([OCV] <= " & Me.Text37 & ") AND "
End If

If Not IsNull(Me.Text26) Then
strWhere = strWhere & "([OCC] >= " & Me.Text26 & ") AND "
End If

If Not IsNull(Me.Text39) Then
strWhere = strWhere & "([OCC] <= " & Me.Text39 & ") AND "
End If

If Not IsNull(Me.Text27) Then
strWhere = strWhere & "([OCFl] >= " & Me.Text27 & ") AND "
End If

If Not IsNull(Me.Text41) Then
strWhere = strWhere & "([OCFl] <= " & Me.Text41 & ") AND "
End If

If Not IsNull(Me.Text28) Then
strWhere = strWhere & "([OCTm] >= " & Me.Text28 & ") AND "
End If

If Not IsNull(Me.Text43) Then
strWhere = strWhere & "([OCTm] <= " & Me.Text43 & ") AND "
End If

If Not IsNull(Me.Text31) Then
strWhere = strWhere & "([OCFWL] >= " & Me.Text31 & ") AND "
End If

If Not IsNull(Me.Text49) Then
strWhere = strWhere & "([OCFWL] <= " & Me.Text49 & ") AND "
End If

If Not IsNull(Me.Text32) Then
strWhere = strWhere & "([OCFA] Like ""*" & Me.Text32 & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

On Error GoTo Err_Filter_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_Filter_Click:
Exit Sub

Err_Filter_Click:
MsgBox Err.Description
Resume Exit_Filter_Click

End Sub