how to add check box's values in to filter string

[sspreyer]

hi

all

I have got like subform which I think is the tech term I use it to filter dates on field "date raised" and open's a report depend on date's entered in txtstartdate and txtenddate it works perfectly but I need to add some check boxes to check other field's are true/false

right I will explain best I can hope every gets it

this the code first

 Private Sub cmdPreview_Click()

    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "Input Report"      'Put your report name in these quotes.
    strDateField = "[Date raised]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
     [COLOR=red]strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
[/COLOR] End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
 Exit_Handler:
    Exit Sub
 Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

all I know is with my very little vba knowledge I need to add code to the text in red well I'm guessing

here goes with what I would like to add
job cancelled1 check box name field name= job cancelled
job on hold1 check box name field name = job on hold
void property1 check box name field name = void property

e.g. if I put date range in my date boxes and tick job cancelled show all record in that date range cancelled same with job on hold and void property

here some code I did but wont work as it not in the same strwhere above

 if me[job cancelled1] = true then
 strwhere = [job cancelled] = 1 'field name 
 else
 strwhere [job cancelled] is null
 end if

some how need to add the strwhere to the code above

please help!!!!!

I'm banging my head against a wall and it starting to hurt lol


thanks in advance

shane

 

[pbaldy]

More like

strwhere = strwhere & " And [job cancelled] = 1"

 

[sspreyer]

More like

strwhere = strwhere & " And [job cancelled] = 1"

Sorry to be a little slow but do I add that to my second lot of vba

Thank you so much for you time

Shane

 

[pbaldy]

That a correction for this line:

strwhere = [job cancelled] = 1

 

[sspreyer]

hi

I'm getting syntax error missing operator I have attach picture so you may understand better then me




cheers
shane

 

[sspreyer]

hi

right this works

  If Me.[Job Cancelled 2] = True Then
     strWhere = strWhere & "AND" & " [job cancelled] = -1 "

but now if I leave the date range box's empty and only select jobs cancelled I get
syntax error



thanks for your time

shane

 

[pbaldy]

You have to add the "And" conditionally, just like you did in the original code.

 

[sspreyer]

thanks paul legend!!!!

cheers
shane

 

[pbaldy]

Happy to help!