Using start date and end to filter subfrom

[Ali]

Hello, I've been using this example to filter a subform using multiple
combo boxes on the main form

http://www.tonyhine.co.uk/photogallery/Downloads/CboBoxesFilterResults_ALLorSome.zip

The only difference with mine is that I use a set filter button where
as the example has the code in the combo box.

Here's the code for the query for the subform, the main form has all
the combo boxes with the set filter button:

SELECT issues.ID, issues.Status, issues.[Assigned To],
IIf([ID],"ViewEdit","") AS ViewEdit, issues.[Opened By],
issues.Project, issues.Category, issues.Priority, issues.Department,
issues.Title
FROM issues
WHERE (((issues.ID) Like fCboSearch([Forms]![Main]![ID])) AND
((issues.Status) Like fCboSearch([Forms]![Main]![Status])) AND
((issues.[Assigned To]) Like fCboSearch([Forms]![Main]![AssignedTo]))
AND ((issues.[Opened By]) Like fCboSearch([Forms]![Main]![OpenedBy]))
AND ((issues.Project) Like fCboSearch([Forms]![Main]![Project])) AND
((issues.Category) Like fCboSearch([Forms]![Main]![Category])) AND
((issues.Priority) Like fCboSearch([Forms]![Main]![Priority])) AND
((issues.Department) Like fCboSearch([Forms]![Main]![Department])) AND
((issues.Title) Like fCboSearch([Forms]![Main]![Text_search])))
ORDER BY issues.Status;

This is the code for the module:
Public Function fCboSearch(vCboSearch As Variant)

If IsNull(vCboSearch) Or vCboSearch = "" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function

And this is what happens when I click Set Filter
Private Sub Search_Click()
fSetVar
End Sub

Private Sub fSetVar()
issueqrysubform.Requery
End Sub


Now what I need help with is that I need to be able to do a text
search from the main form using an unbound text box and then clicking
the set filter button to do the search. Also using the same set filter
button I want to be able to search for records using data ranges. I
have all the textboxes setup for the calendar dates, I just can't
understand the coding behind it to do the actual search.

Thanks.

 

[Ken Sheridan]

As I explained in your other thread, the drawback of this approach is that it
does not allow for Nulls in any of the fields. Because comparative
operations, including Like, always return Null when comparing anything with a
Null then a row would not be returned where a field contains a Null where no
selection has been made in the control on the main form corresponding to that
field.

The alternative approach is simpler and needs no code other than requerying
the subform in your button's Click event procedure. Its done by referencing
each control as a parameter and testing for each being OR IS NULL in the
subform's RecordSource query. For this example I've assumed that the date
range is defined by controls txtStart and txtEnd on the main form and the
field containing the dates is called OrderDate:

PARAMETERS
Forms!Main!txtStart DATETIME,
Forms!Main!txtEnd DATETIME;
SELECT ID, Status, [Assigned To],
IIf([ID],"ViewEdit","") AS ViewEdit, [Opened By],
Project, Category, Priority, Department, Title
FROM issues
WHERE (ID = [Forms]![Main]![ID]
OR [Forms]![Main]![ID] IS NULL)
AND (Status = [Forms]![Main]![Status]
OR [Forms]![Main]![Status] IS NULL)
AND ([Assigned To] = [Forms]![Main]![AssignedTo]
OR [Forms]![Main]![AssignedTo] IS NULL)
AND ([Opened By] = [Forms]![Main]![OpenedBy]
OR [Forms]![Main]![OpenedBy] IS NULL)
AND (Project = [Forms]![Main]![Project]
OR [Forms]![Main]![Project] IS NULL)
AND (Category = Forms]![Main]![Category]
OR Forms]![Main]![Category] IS NULL)
AND (Priority = [Forms]![Main]![Priority]
OR [Forms]![Main]![Priority] IS NULL)
AND (Department = [Forms]![Main]![Department]
OR [Forms]![Main]![Department] IS NULL)
AND (Title LIKE "*" & [Forms]![Main]![Text_search] & "*"
OR [Forms]![Main]![Text_search] IS NULL)
AND (OrderDate >= Forms!Main!txtStart
OR Forms!Main!txtStart IS NULL)
AND (OrderDate < DATEADD("d",1,Forms!Main!txtEnd)
OR Forms!Main!txtEnd IS NULL)
ORDER BY Status;

If you change your subform's RecordSource to the above, amending the field
and control names for the date search to the names of your real ones, then
you should find that your existing button will work without any changes to
the VBA code. The fCboSearch function won't be doing anything, so could be
deleted, but is harmless if left in place.

Ken Sheridan
Stafford, England

Hello, I've been using this example to filter a subform using multiple
combo boxes on the main form

http://www.tonyhine.co.uk/photogallery/Downloads/CboBoxesFilterResults_ALLorSome.zip

The only difference with mine is that I use a set filter button where
as the example has the code in the combo box.

Here's the code for the query for the subform, the main form has all
the combo boxes with the set filter button:

SELECT issues.ID, issues.Status, issues.[Assigned To],
IIf([ID],"ViewEdit","") AS ViewEdit, issues.[Opened By],
issues.Project, issues.Category, issues.Priority, issues.Department,
issues.Title
FROM issues
WHERE (((issues.ID) Like fCboSearch([Forms]![Main]![ID])) AND
((issues.Status) Like fCboSearch([Forms]![Main]![Status])) AND
((issues.[Assigned To]) Like fCboSearch([Forms]![Main]![AssignedTo]))
AND ((issues.[Opened By]) Like fCboSearch([Forms]![Main]![OpenedBy]))
AND ((issues.Project) Like fCboSearch([Forms]![Main]![Project])) AND
((issues.Category) Like fCboSearch([Forms]![Main]![Category])) AND
((issues.Priority) Like fCboSearch([Forms]![Main]![Priority])) AND
((issues.Department) Like fCboSearch([Forms]![Main]![Department])) AND
((issues.Title) Like fCboSearch([Forms]![Main]![Text_search])))
ORDER BY issues.Status;

This is the code for the module:
Public Function fCboSearch(vCboSearch As Variant)

If IsNull(vCboSearch) Or vCboSearch = "" Then
fCboSearch = "*"
Else
fCboSearch = vCboSearch
End If
End Function

And this is what happens when I click Set Filter
Private Sub Search_Click()
fSetVar
End Sub

Private Sub fSetVar()
issueqrysubform.Requery
End Sub


Now what I need help with is that I need to be able to do a text
search from the main form using an unbound text box and then clicking
the set filter button to do the search. Also using the same set filter
button I want to be able to search for records using data ranges. I
have all the textboxes setup for the calendar dates, I just can't
understand the coding behind it to do the actual search.

Thanks.

 

[Ali]

That worked great! Thank you so much.

Real quick, if I wanted to clear all the fields and then display all
the records in the subform using a button...how would I go about doing
that?

Thanks.

 

[Ali]

I found it...thanks!

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Or _
TypeOf ctl Is ListBox Or TypeOf ctl Is CheckBox Then
'
If Left(ctl.ControlSource, 1) <> "=" Then
ctl.Value = Null
End If
End If
Next

 

[Ken Sheridan]

The method I usually use for this sort of thing is similar, but I set the Tag
property of each of the controls in question to some value, which can be
anything you like. You can then identify the controls to clear by means of
the Tag property:

Dim ctl As Control

On Error Resume Next
For Each ctl In Me.Controls
If ctl.Tag = "ClearMe" Then
ctl = Null
End If
Next ctl

The error handling caters for controls without a Tag property.

Ken Sheridan
Stafford, England