Query Yes/No data via Forms

[Strugglin]

I want to create a form that a user can use to query my database. The user will choose a date range (BeginDate, EndDate), then choose which checkbox items to query for, TR11, TR12 or TR13.

Here's my table:
Date - Date
TR11 - Yes/No
TR12 - Yes/No
TR13 - Yes/No

Here's my form:
Begin Date ____
End Date _____
__ TR11
__ TR12


__ TR13

I created a form that properly queried the chosen date range but I can't figure out how to modify the query so that when the user checks TR11 on the form, and only the records that fall within the date range and contain a Yes value in TR11 will be displayed.

Please help me figure this out!

[June7]

Does this tutorial help: http://datapigtechnologies.com/flash...earchform.html

[Strugglin]

Great tutorial but doesn't address check boxes. I'll looking more for a query that returns the records when the record contains Yes in TR11 field & TR11 box is checked Yes on form. Thanks though

[June7]

Are you opening a form or report with the user's selected criteria? I use VBA to construct a filter string based on user choices then open the form/report with that string as the WHERE CLAUSE in DoCmd.OpenForm (or OpenReport).

[Strugglin]

Can you instruct me on how to do that and post the code if you have it?

[June7]

The checkbox can be only Yes or No (not triple state - set checkbox default value to No)? If checked, Use that field as criteria, if not checked, ignore that field? I might have a working query. I created fields with expressions, one for each checkbox and set the criteria for these fields to <> False:
Iif(Forms!formname!chkTR11=0, [TR11]=0 OR [TR11]=-1,[TR11]=-1)


I will throw this code from my project at you:

Private Sub btnFind_Click()
Dim strSQL As String
Dim strField1 As String, strField2 As String, strFor1 As String, strFor2 As String
Dim strField As String, strStart As String, strEnd As String
With Me
.grpFilter = Null
.lbxQueries = Null
strFor1 = Nz(.cbxFor1, "")
strFor2 = Nz(.cbxFor2, "")
strField1 = CustomQuerySettings(Nz(.cbxField1, ""))
strField2 = CustomQuerySettings(Nz(.cbxField2, ""))
strField = CustomQuerySettings(Nz(.cbxCategory, ""))
If strField = "LabNum" Then
strStart = "'" & Nz(.tbxStart, "") & "'"
strEnd = "'" & Nz(.tbxEnd, "") & "'"
Else
strStart = "#" & Nz(.tbxStart, "") & "#"
strEnd = "#" & Nz(.tbxEnd, "") & "#"
End If
strSQL = IIf(IsNull(.cbxField1) Or (Not IsNull(.cbxField1) And IsNull(.cbxFor1)), "", strField1 & "='" & strFor1 & "'")
strSQL = strSQL & IIf(IsNull(.cbxField2) Or (Not IsNull(.cbxField2) And IsNull(.cbxFor2)), "", IIf(strSQL = "", "", " AND ") & strField2 & "='" & strFor2 & "'")
strSQL = strSQL & IIf(IsNull(.cbxCategory) Or (Not IsNull(.cbxCategory) And (IsNull(.tbxStart) Or IsNull(.tbxEnd))), "", IIf(strSQL = "", "", " AND ") & strField & " BETWEEN " & strStart & " AND " & strEnd)
strSQL = strSQL & IIf(IsNull(.cbxTest), "", IIf(strSQL = "", "", " AND ") & "TestNum='" & .cbxTest & "'")
If strSQL = "" Then
MsgBox "Must select criteria!", vbOKOnly, "EntryError"
ElseIf Not IsNull(.cbxTest) Then
.ctrSampleList.Form.RecordSource = "SELECT Submit.*, TestNum, StateNum, ProjectName, ProgCode, LedgerCode, Colo, FedNum, Metric, Remark1, Remark2, Remark3, Remark4, Remark5, Remark6 " & _
"FROM ((Submit LEFT JOIN Projects ON Submit.ProjRecID = Projects.ProjRecID) LEFT JOIN Remarks ON Submit.LabNum = Remarks.LabNum) " & _
"LEFT JOIN Tests ON Submit.LabNum = Tests.LabNum " & _
"ORDER BY Submit.LabNum DESC;"
End If
.ctrSampleList.Form.Filter = strSQL
.ctrSampleList.Form.FilterOn = True
.tbxLABNUM.SetFocus
End With
End Sub

Also, check this thread http://bytes.com/topic/access/answer...box-parameters

[Strugglin]

Thank you for your help, I'll give that a try!