Results 1 to 5 of 5
  1. #1
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36

    how to filter a date range and check another field is true with check box syntax err

    hi all

    I have a sub form well I think that's the tech term right let get, down to business

    how it works

    I have 2 txt box's called txtstartdate and txtenddate I enter date range between these box's and hit the command button it show all records in that date range. the field the txt box's are filtering is "date Raised" then open's report call "input report" I hope everyone with me lol. if I leave txtstartdate and txtenddate empty "no dates" it will open my report showing all records yet again that great


    I have now added checkbox called job cancelled 2 and this filter's field "job cancelled"

    right here the code then I will explain more
    Code:
    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
         strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
     End If
        If Me.[Job Cancelled 2] = True Then
             strWhere = strWhere & "AND" & "[job cancelled] = -1"
           
           
            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
    r

    right the code in red is my new code I have entered right now here's my problem

    if I enter a date range in my txt box's and make job cancelled 2 " true"
    it opening report showing job cancelled in that date range

    but my problem is if I enter no date's in my txt box's and make job cancelled 2 true
    I get syntax error


    Here's a pic
    Click image for larger version. 

Name:	1391396599_tmp_syntax_2.jpg 
Views:	10 
Size:	10.3 KB 
ID:	15245

    so i would like to be able to not have to enter a date range unless the user wants to and if job cancelled 2 is true show all records job cancelled



    hope you all understand my poor English lol

    I have been bang my head against the wall for last few days on this now and it begin to hurt
    as my vba skill are very little

    please I'm begging for help I want to get this sorted first one to solve I owe them a beer!!! BIG TIME!!


    cheers

    Shane

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need to add the "And" conditionally, just as you did in the date portion.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    Quote Originally Posted by pbaldy View Post
    You need to add the "And" conditionally, just as you did in the date portion.
    sorry pbaldy

    but don't understand what you mean I have tried

    this but does work

    Code:
        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
         strWhere = strWhere & "AND" & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
     End If
        If Me.[Job Cancelled 2] = True Then
             strWhere = strWhere & "AND" & "[job cancelled] = -1"
           
           
            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
    thanks

    shane

  4. #4
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    Hi Paul

    I finally got it! the penny has totally dropped cheer mate for the help

    Code:
     If Me.[Job Cancelled 2] = True Then
          If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
                 End If
              strWhere = strWhere & "[job cancelled] = -1"

    Paul you legend !!
    owe you a beer if in London!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! If my daughter still lived in England, I'd take you up on it. Went there a few times while she was there. Really appreciated those signs on the sidewalk warning tourists like me to look right instead of left first! You must have lost a few before putting those up.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 01-23-2014, 03:24 PM
  2. Replies: 4
    Last Post: 01-14-2014, 01:28 PM
  3. Query to Pull from Date Range & Check 2 Areas
    By esh112288 in forum Queries
    Replies: 9
    Last Post: 11-12-2013, 10:05 PM
  4. Replies: 3
    Last Post: 10-25-2012, 10:04 PM
  5. Multiple Field & date range filter
    By mrkandohi001 in forum Reports
    Replies: 6
    Last Post: 01-18-2012, 03:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums