Filtering data with date and check boxes

[raffyT]

Hi.. I am new in MS Access programming and is trying to create a search form for all records based on selected dates and check box. I was able to filter the records by dates but can't find ways to filter the records that meet the dates to be filtered again based on the selected check box/s. Basically what I wanted to is display all the records within the selected dates and check box/s.

[burrina]

Add the check box criteria to your query. Example: True or -1 for Person1

HTH

[raffyT]

Add the check box criteria to your query. Example: True or -1 for Person1

HTH

Thanks for the reply..

this is what I have on the Click Event. What I want to do is to display record/s of person/s that I tick (checkbox) that match the date I selected. I was able to do it if one name is checked, however not if two or more names is ticked.. (btw, I used 1, 2, 3,4 as filter for each name.)

Private Sub cmdbydate_Click()
On Error GoTo cmdbydate_Click_Err
If (chkPersonl = -1) Then
DoCmd.SetFilter "", "[OpenedBy] Like ""1"" And [OpenedDate] Between [txtStartDate] And [txtEndDate]", "SearchResult"
ElseIf (chkPerson3 = -1) Then
DoCmd.SetFilter "", "[OpenedBy] Like ""2"" And [OpenedDate] Between [txtStartDate] And [txtEndDate]", "SearchResult"
ElseIf (chkPerson4 = -1) Then
DoCmd.SetFilter "", "[OpenedBy] Like ""3"" And [OpenedDate] Between [txtStartDate] And [txtEndDate]", "SearchResult"
If (chkPersonl = -1) Then
DoCmd.SetFilter "", "[OpenedBy] Like ""4"" And [OpenedDate] Between [txtStartDate] And [txtEndDate]", "SearchResult"
Else
DoCmd.SetFilter "ShowAll", "[OpenedBy] Like ""*"" And [OpenedDate] Between [txtStartDate] And [txtEndDate]", "SearchResult"
End If

cmdbydate_Click_Exit:
Exit Sub
cmdbydate_Click_Err:
MsgBox Error$
Resume cmdbydate_Click_Exit
End Sub

[burrina]

Do you ONLY want to allow one check box to be checked? If so, use the AfterUpdateEvent to Disable the others.

[raffyT]

nope. I want to display records of all the names that has been checked.

[burrina]

So, then do you have All of those persons in your query? i.e. Person1,Person2 etc...?

[raffyT]

I was able to display the record correctly if only ONE name is checked, but NOT if two or more is checked.

[burrina]

You already said that, I asked you if ALL of those Persons were in your query grid! Are they? Post your query record source for your search.
Do you have 2, 3 and 4 values in your query?

[raffyT]

Hi.. sorry but I'm totally lost here.. is this the one you're asking..

SELECT Issues.OpenedBy, Issues.OpenedDate, Issues.Ticket, Issues.Server, Issues.Priority, Issues.AssignedTo, Issues.Status
FROM Issues
WHERE (((Issues.OpenedBy) In (SELECT [OpenedBy] FROM [Issues] As Tmp GROUP BY [OpenedBy],[OpenedDate] HAVING Count(*)>1 And [OpenedDate] = [Issues].[OpenedDate])))
ORDER BY Issues.OpenedBy, Issues.OpenedDate;

[June7]

Cannot build filter criteria based on these multiple checkboxes into the query (or at least it would be very difficult). Use VBA to construct filter string and apply to Filter property of form or pass to form or report when opened.

Review:
http://www.allenbrowne.com/ser-62code.html
http://www.allenbrowne.com/ser-50.html

You need to build an IN clause for the multiple parameters for the OpenedBy field or an expression with OR operators. The second link shows code for selecting multiple items with a listbox to construct IN clause.

Advantage of listbox over checkboxes is if need more or less than 4 persons or change person names, don't have to modify form design.