Missing Records

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

I have created a form for passing values to a query that
has a check box and 3 combo boxes that get their values
from a table. The problem is that is if any of the
criteria fields in the record are blank then that record
is not returned by the query.

I have use the following expression in the search forms
record source query to refer to the filterby form

Like [forms]![Filterby]![ckbxMembership] & "*"

Like [forms]![Filterby]![Town] & "*"

Like [forms]![Filterby]![BusinessType] & "*"

Like [forms]![Filterby]![MembershipLevel] & "*"

Tried adding or Is Null to each expression but this just
returned any records that the field was null as well as
records that met the criteria.

I could use validation to ensure that the fields have
data in them but this is a database that I'm modifying
with a lot of records in it so I was looking for a way
around this.
I really don't have much experience with access and tend
to use trial and error to get stuff to work. So far this
has been all error and very trying.

any help would be greatly appreciated

Trevor
 
One way to fix this is to add a zero length string to the field. This
results in a where clause similar to:
WHERE [Membership] & "" Like [forms]![Filterby]![ckbxMembership] & "*"
 
Trevor, the most efficient way to handle this is to build a filter string
consisting of only those boxes that have a value, and then either apply a
Filter to the form or reassign its RecordSource property.

Remove the references to the form and its controls from your query. Then
with the "Search" button on your form, do something like this:

Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long

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

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

'etc for other boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <=0 Then
MsgBox "No criteria"
Else
With Forms("YourOtherFormNameHere")
.Filter = Left(strWhere, lngLen)
.FilterOn = True
End With
End If
End Sub
 
I have created a form for passing values to a query that
has a check box and 3 combo boxes that get their values
from a table. The problem is that is if any of the
criteria fields in the record are blank then that record
is not returned by the query.
Click to expand...

Try using criteria such as

=[forms]![Filterby]![ckbxMembership] OR
[forms]![Filterby]![ckbxMembership] IS NULL
 
Thanks John, I thought I had tried the Is Null in every
combination I could think of to get this to work. Once
again thanks heaps for everyone's replys.

Regards Trevor
-----Original Message-----
I have created a form for passing values to a query that
has a check box and 3 combo boxes that get their values
from a table. The problem is that is if any of the
criteria fields in the record are blank then that record
is not returned by the query.
Click to expand...

Try using criteria such as

=[forms]![Filterby]![ckbxMembership] OR
[forms]![Filterby]![ckbxMembership] IS NULL





.
Click to expand...
 
Back
Top