Filter form criteria on multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to create a form to retrieve selected records based on the criteria I
select from a members database. The new form is based on a MainMemberForm and
the citeria I want to search on are: LastName, PostalCode, StartDate, Gender,
JobTitle, and/or MemberID.

I want the form to display the records in a datasheet and then have the
option of printing that data.

I am in over my head with codes and have created nothing but a mess. Can
anyone help?

Thanks
 
If you post your mess, we can try to tidy it up for you, otherwise it's hard
to gauge your level of knowledge, and describing / writing the code for you
from scratch without knowing a single table, field, control, form or button
name is nigh on impossible!! :p

As a starting guide you would probably be best off having a continuous form
with a row of comboboxes in the form header, and then creating an SQL SELECT
statement to provide the forms recordsource depending on the selections made,
you could use a modified version of this to select the records to be passed
to a Report (only way to print with any control in Access).

hope this gets you started,

TonyT..
 
Thanks for the very quick response.

The table where the data comes from is called "Members"...

Here's the mess!

Private Sub cmdFilter_Click()
If Not IsNull(Me.txtFilterCity) Then
[City] & "([City] = """ & Me.txtFilterCity & """) AND "
End If

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

If Not IsNull(Me.cboFilterCategory) Then
[Category Name] = [Category Name] & "([Category Name] = " &
Me.cboFilterCategory & ") AND "
End If

If Me.cboFilterBenefits = -1 Then
[Benefits] = [Benefits] & "([Benefits] = True) AND "
ElseIf Me.cboFilterBenefits = 0 Then
[Benefits] = [Benefits] & "([Benefits] = False) AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] >= " &
Format(Me.txtStartDate) & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] < " & Format(Me.txtEndDate
+ 1) & ")"
End If

End Sub

There was another post that I can no longer locate that I based the form on
to begin with and tried to amend the code for my form. Didn't do so well as
you can see.

Thanks for your help...
 
Try;
Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND City = '" & Me.txtFilterCity &
"'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If


If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "City = '" & Me.txtFilterLastName & "'"
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub

Thats only the first 2 done for you and the actual run filter bit which
should remain at the end after you have done the rest of the fields, and
watch out for wordwrap.

The cboFilterBenefits can be done the same way - access accepts -1 or True
when evaluating these thingsbut remove the two ' from that line, also for the
dates replace ' with # (basically around the me.txtFilter...... bits so;
"City = '" & Me.txtFilterLastName & "'"
would become
"City = #" & Me.txtFilterLastName & "#"
if Me.txtFilterLastName were a date or
"City = " & Me.txtFilterLastName & ""
if it were a number or boolean yes/no field (like benefits)

hope that makes sense, would have been quicker to type all the code for you
rather than try to explain it all afterwards!!! :p

Good luck,

TonyT..

MMM Ottawa said:
Thanks for the very quick response.

The table where the data comes from is called "Members"...

Here's the mess!

Private Sub cmdFilter_Click()
If Not IsNull(Me.txtFilterCity) Then
[City] & "([City] = """ & Me.txtFilterCity & """) AND "
End If

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

If Not IsNull(Me.cboFilterCategory) Then
[Category Name] = [Category Name] & "([Category Name] = " &
Me.cboFilterCategory & ") AND "
End If

If Me.cboFilterBenefits = -1 Then
[Benefits] = [Benefits] & "([Benefits] = True) AND "
ElseIf Me.cboFilterBenefits = 0 Then
[Benefits] = [Benefits] & "([Benefits] = False) AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] >= " &
Format(Me.txtStartDate) & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] < " & Format(Me.txtEndDate
+ 1) & ")"
End If

End Sub

There was another post that I can no longer locate that I based the form on
to begin with and tried to amend the code for my form. Didn't do so well as
you can see.

Thanks for your help...

TonyT said:
If you post your mess, we can try to tidy it up for you, otherwise it's hard
to gauge your level of knowledge, and describing / writing the code for you
from scratch without knowing a single table, field, control, form or button
name is nigh on impossible!! :p

As a starting guide you would probably be best off having a continuous form
with a row of comboboxes in the form header, and then creating an SQL SELECT
statement to provide the forms recordsource depending on the selections made,
you could use a modified version of this to select the records to be passed
to a Report (only way to print with any control in Access).

hope this gets you started,

TonyT..
Click to expand...
Click to expand...
 
Thanks so much for the help...but...I still must be doing something wrong. It
doesn't report that I have errors any more but it doesn't actually search for
anything either...

Here's what it looks like now...am I on the right track yet?

Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then strMyFilter = strMyFilter & " AND City = '" &
Me.txtFilterCity & "'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If

If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "City = '" & Me.txtFilterLastName & "'"
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub



TonyT said:
Try;
Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND City = '" & Me.txtFilterCity &
"'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If


If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "City = '" & Me.txtFilterLastName & "'"
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub

Thats only the first 2 done for you and the actual run filter bit which
should remain at the end after you have done the rest of the fields, and
watch out for wordwrap.

The cboFilterBenefits can be done the same way - access accepts -1 or True
when evaluating these thingsbut remove the two ' from that line, also for the
dates replace ' with # (basically around the me.txtFilter...... bits so;
"City = '" & Me.txtFilterLastName & "'"
would become
"City = #" & Me.txtFilterLastName & "#"
if Me.txtFilterLastName were a date or
"City = " & Me.txtFilterLastName & ""
if it were a number or boolean yes/no field (like benefits)

hope that makes sense, would have been quicker to type all the code for you
rather than try to explain it all afterwards!!! :p

Good luck,

TonyT..

MMM Ottawa said:
Thanks for the very quick response.

The table where the data comes from is called "Members"...

Here's the mess!

Private Sub cmdFilter_Click()
If Not IsNull(Me.txtFilterCity) Then
[City] & "([City] = """ & Me.txtFilterCity & """) AND "
End If

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

If Not IsNull(Me.cboFilterCategory) Then
[Category Name] = [Category Name] & "([Category Name] = " &
Me.cboFilterCategory & ") AND "
End If

If Me.cboFilterBenefits = -1 Then
[Benefits] = [Benefits] & "([Benefits] = True) AND "
ElseIf Me.cboFilterBenefits = 0 Then
[Benefits] = [Benefits] & "([Benefits] = False) AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] >= " &
Format(Me.txtStartDate) & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] < " & Format(Me.txtEndDate
+ 1) & ")"
End If

End Sub

There was another post that I can no longer locate that I based the form on
to begin with and tried to amend the code for my form. Didn't do so well as
you can see.

Thanks for your help...

TonyT said:
If you post your mess, we can try to tidy it up for you, otherwise it's hard
to gauge your level of knowledge, and describing / writing the code for you
from scratch without knowing a single table, field, control, form or button
name is nigh on impossible!! :p

As a starting guide you would probably be best off having a continuous form
with a row of comboboxes in the form header, and then creating an SQL SELECT
statement to provide the forms recordsource depending on the selections made,
you could use a modified version of this to select the records to be passed
to a Report (only way to print with any control in Access).

hope this gets you started,

TonyT..

:

Trying to create a form to retrieve selected records based on the criteria I
select from a members database. The new form is based on a MainMemberForm and
the citeria I want to search on are: LastName, PostalCode, StartDate, Gender,
JobTitle, and/or MemberID.

I want the form to display the records in a datasheet and then have the
option of printing that data.

I am in over my head with codes and have created nothing but a mess. Can
anyone help?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks Tony. I don't get error messages anymore, but I also don't get
anything at all!

Not sure what I am doing wrong now!

M

TonyT said:
Try;
Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND City = '" & Me.txtFilterCity &
"'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If


If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "City = '" & Me.txtFilterLastName & "'"
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub

Thats only the first 2 done for you and the actual run filter bit which
should remain at the end after you have done the rest of the fields, and
watch out for wordwrap.

The cboFilterBenefits can be done the same way - access accepts -1 or True
when evaluating these thingsbut remove the two ' from that line, also for the
dates replace ' with # (basically around the me.txtFilter...... bits so;
"City = '" & Me.txtFilterLastName & "'"
would become
"City = #" & Me.txtFilterLastName & "#"
if Me.txtFilterLastName were a date or
"City = " & Me.txtFilterLastName & ""
if it were a number or boolean yes/no field (like benefits)

hope that makes sense, would have been quicker to type all the code for you
rather than try to explain it all afterwards!!! :p

Good luck,

TonyT..

MMM Ottawa said:
Thanks for the very quick response.

The table where the data comes from is called "Members"...

Here's the mess!

Private Sub cmdFilter_Click()
If Not IsNull(Me.txtFilterCity) Then
[City] & "([City] = """ & Me.txtFilterCity & """) AND "
End If

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

If Not IsNull(Me.cboFilterCategory) Then
[Category Name] = [Category Name] & "([Category Name] = " &
Me.cboFilterCategory & ") AND "
End If

If Me.cboFilterBenefits = -1 Then
[Benefits] = [Benefits] & "([Benefits] = True) AND "
ElseIf Me.cboFilterBenefits = 0 Then
[Benefits] = [Benefits] & "([Benefits] = False) AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] >= " &
Format(Me.txtStartDate) & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] < " & Format(Me.txtEndDate
+ 1) & ")"
End If

End Sub

There was another post that I can no longer locate that I based the form on
to begin with and tried to amend the code for my form. Didn't do so well as
you can see.

Thanks for your help...

TonyT said:
If you post your mess, we can try to tidy it up for you, otherwise it's hard
to gauge your level of knowledge, and describing / writing the code for you
from scratch without knowing a single table, field, control, form or button
name is nigh on impossible!! :p

As a starting guide you would probably be best off having a continuous form
with a row of comboboxes in the form header, and then creating an SQL SELECT
statement to provide the forms recordsource depending on the selections made,
you could use a modified version of this to select the records to be passed
to a Report (only way to print with any control in Access).

hope this gets you started,

TonyT..

:

Trying to create a form to retrieve selected records based on the criteria I
select from a members database. The new form is based on a MainMemberForm and
the citeria I want to search on are: LastName, PostalCode, StartDate, Gender,
JobTitle, and/or MemberID.

I want the form to display the records in a datasheet and then have the
option of printing that data.

I am in over my head with codes and have created nothing but a mess. Can
anyone help?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
My mistake on one bit I see there, Its searching for City's =
me.txtFilterLastName so probably won't find any!
Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND City = '" & Me.txtFilterCity &
"'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If


If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "LastName = '" & Me.txtFilterLastName & "'" 'code changed here
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub
Click to expand...

Try That and see how you go, Try 1 search criteria at a time and see if you
get responses, and set a breakpoint or 2 in the code and check the value of
strMyFilter as the code runs.

TonyT..

MMM Ottawa said:
Thanks Tony. I don't get error messages anymore, but I also don't get
anything at all!

Not sure what I am doing wrong now!

M

TonyT said:
Try;
Private Sub cmdFilter_Click()

Dim strMyFilter As String

strMyFilter = ""

If Not IsNull(Me.txtFilterCity) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND City = '" & Me.txtFilterCity &
"'"
Else
strMyFilter = "City = '" & Me.txtFilterCity & "'"
End If
End If


If Not IsNull(Me.txtFilterLastName) Then
If strMyFilter <> "" Then
strMyFilter = strMyFilter & " AND Lastname = '" &
Me.txtFilterLastName & "'"
Else
strMyFilter = "City = '" & Me.txtFilterLastName & "'"
End If
End

If strMyFilter <> "" Then
Me.Filter = strMyFilter
Else
MsgBox "No search criteria entered"
End If

End Sub

Thats only the first 2 done for you and the actual run filter bit which
should remain at the end after you have done the rest of the fields, and
watch out for wordwrap.

The cboFilterBenefits can be done the same way - access accepts -1 or True
when evaluating these thingsbut remove the two ' from that line, also for the
dates replace ' with # (basically around the me.txtFilter...... bits so;
"City = '" & Me.txtFilterLastName & "'"
would become
"City = #" & Me.txtFilterLastName & "#"
if Me.txtFilterLastName were a date or
"City = " & Me.txtFilterLastName & ""
if it were a number or boolean yes/no field (like benefits)

hope that makes sense, would have been quicker to type all the code for you
rather than try to explain it all afterwards!!! :p

Good luck,

TonyT..

MMM Ottawa said:
Thanks for the very quick response.

The table where the data comes from is called "Members"...

Here's the mess!

Private Sub cmdFilter_Click()
If Not IsNull(Me.txtFilterCity) Then
[City] & "([City] = """ & Me.txtFilterCity & """) AND "
End If

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

If Not IsNull(Me.cboFilterCategory) Then
[Category Name] = [Category Name] & "([Category Name] = " &
Me.cboFilterCategory & ") AND "
End If

If Me.cboFilterBenefits = -1 Then
[Benefits] = [Benefits] & "([Benefits] = True) AND "
ElseIf Me.cboFilterBenefits = 0 Then
[Benefits] = [Benefits] & "([Benefits] = False) AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] >= " &
Format(Me.txtStartDate) & ") AND "
End If

If Not IsNull(Me.txtStartDate) Then
[StartDate] = [StartDate] & "([StartDate] < " & Format(Me.txtEndDate
+ 1) & ")"
End If

End Sub

There was another post that I can no longer locate that I based the form on
to begin with and tried to amend the code for my form. Didn't do so well as
you can see.

Thanks for your help...

:

If you post your mess, we can try to tidy it up for you, otherwise it's hard
to gauge your level of knowledge, and describing / writing the code for you
from scratch without knowing a single table, field, control, form or button
name is nigh on impossible!! :p

As a starting guide you would probably be best off having a continuous form
with a row of comboboxes in the form header, and then creating an SQL SELECT
statement to provide the forms recordsource depending on the selections made,
you could use a modified version of this to select the records to be passed
to a Report (only way to print with any control in Access).

hope this gets you started,

TonyT..

:

Trying to create a form to retrieve selected records based on the criteria I
select from a members database. The new form is based on a MainMemberForm and
the citeria I want to search on are: LastName, PostalCode, StartDate, Gender,
JobTitle, and/or MemberID.

I want the form to display the records in a datasheet and then have the
option of printing that data.

I am in over my head with codes and have created nothing but a mess. Can
anyone help?

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Filter from on multiple criteria 1
Multiple criteria in Form 1
Combo box question on a form 3
Multiple Filter Criteria 4
Set value in one field based on selection from lookup field 7
Change to form 3
Need to set default filter on form 9
Select Report Fields based on filtered form 0

Back
Top