Code in Forms/Querry

[Alexandra504]

I am creating a form in which the user will enter some data in some fields
(not all the fields will be fill out with data).

Let's say that in the form I have 2 fiels for now: Member_ID and Tier. If I
enter both a Member-ID in the form (for example 1100000031) and the tier
number (for example 9) and then I hit the 'OK' button it works fine (it
retrieves all the records from the querry with both the member ID 1100000031
and tier 9).

The problem is when I enter just one value in the form (either member id or
tier) and I leave the other field blank, the query comes up with no records.
I would like a code that retrieves records from the querry if I just enter
data in one field on the database.

This is the code that I have in the criteria field in the querry:

[Forms]![Enter Info Form]![Member_ID]

[Forms]![Enter Info Form]![Call_Date]

Any suggestions?

 

[Jeff L]

I think this would be better if you used a filter instead. You would
need to check to see if your fields have a value or not and then build
your filter appropriately.

Dim MyFilter as String

If Nz(Me.[Member_ID]) then
MyFilter = "Member_ID = " & Me.Member_ID
End IF

If Nz(Me.[Call_Date]) then
If Nz(MyFilter) Then
MyFilter = MyFilter & " And Call_Date = '" & Me.Call_Date & "'"
Else
MyFilter = "Call_Date = '" & Me.Call_Date & "'"
End IF
End IF

Me.Filter = MyFilter
Me.FilterOn = True

You will want a way to turn of the filter too, Me.FilterOn = False

Hope that helps!