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

    How to check for start date in date range and combo box value only

    Hi all



    I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show all jobs


    here the code I have got and need help adjusting as my VBA knowledge is little to none


    Code:
    Private Sub cmdPreview_Click()
    'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
            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 start]" '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
        
        '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 in advance you ms access gods

    Shane

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What happens when you try to run the code?
    Where did you get the code?

    For info on vba debugging see http://www.cpearson.com/excel/DebuggingVBA.aspx

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Orange,

    It is code from Allen Browne's site, Method 2
    http://allenbrowne.com/casu-08.html

    I agree, need to know what the error are.....



    ----------------
    thanks in advance you ms access gods
    (Congratulations!... didn't know you had been promoted - but would tend to agree)

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Steve, I hadn't picked up on the elevated status until I reread the post based on your comment.

    I hear it's 8C (46 F) in Whitehorse. How are things in Anchorage.
    I'm in Palm Beach now it's about 68F today, but cooler in North FL.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Supposed to hit 41F today and rain.
    Looks like the same for the rest of the week.

    68F looks really good!! soon - maybe in 3 - 4 months

  6. #6
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    hi the code works perfectly just want to add extra combo box filter. I have field called client and would like to filter with in date range depend on the value in cboclient so for e.g. if I had "peter" in client cboclient and 01-01-2014 in txtstartdate and 15-01-2013 in txtenddate only show records from that client in date range between txtstartdate and txtenddate also if cboclient is empty show all records in a date range

    here what I have tried I get no error but doesn't filter in red what I have changed


    Code:
    Private Sub cmdPreview_Click()
    'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
        
        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 1]" '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.cboclient) Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "( Client = " & Me.cboclient & ")"
        End If
         
        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
       
        
        '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 in advance

    shane

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You don't want this
    If IsDate(Me.cboclient) Then
    This is checking to see if cboClient is a valid Date?????????????

    What is the rowsource of cboclient?

  8. #8
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    hi
    orange

    cboclient is unbound combo box with field list

    strWhere = strWhere & "( Client = " & Me.cboclient & ")" red is my table field

    sorry my vba knowledge is little

    thanks for your time

    shane

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So you need to look at the flow of the code.

    1) The first filter looks at the start date. Is what is entered in the date field a date? Notice "strWhere".... if the start date is a date, strWhere has a value.

    2) Next comes the end date. Is what is entered in the date field a date? Notice "strWhere" now. If a date, then add the next condition. But you have to add a connector. So it becomes

    strWhere = strWhere & " AND "

    Then the end date is concatenated.

    3) Now you want to add a person. Not a date, so can't use the IsDate() function. But you only want the name added if it is non-blank (not NULL). Check to see if the length is >0. If so, then add the name the same way as the end date was concatenated.

    But text strings need to be delimited. Dates are delimited with the hash mark (aka the pound sign > "#"... see the constant declaration for strcJetDate)
    Text strings are delimited with single quotes or double quotes. read up on delimiters.

    Learn how to debug - aka set a break point and single step through the code. That is where the line

    Debug.Print strWhere

    comes in.

    I know I should let you struggle with the code, but I'm feeling generous today. It is really warm here.. snow is melting
    Code:
    Private Sub cmdPreview_Click()
    'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
        
        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 1]" '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 len(trim(Me.cboclient)) > 0 Then
            If strWhere <> vbNullString Then
                strWhere = strWhere & " AND "
            End If
            strWhere = strWhere & "(Client = '" & Me.cboclient & "')"
        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

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The intrinsic function IsDate() is checking for a valid date.

    If your compbo doesn't have a table/query rowsource, then it's probably a value list.

    We need a screen shot or a copy of the db to be of any help.
    Perhaps someone else understands your post better than I do.

    Here are some samples dealing with combo boxes.

  11. #11
    sspreyer is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Nov 2013
    Posts
    36
    thanks you Steve you legend!!!! not only for the code but for the pointer's and advice as I said I very new to all this programming starting getting my head around it

    Glad the Snow is melting must say I do love Ice Road Trucker's

    rather snow then rain though feels like its only rains in London

    yet again steve thanks for the time hope you have great day!!!!

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Steve, you GOD, I hadn't seen your response. I just knew he didn't want to be checking his "client Name" to see if it was a valid date.

    Good eye Steve.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No, Orange, you're the God. I'm a legend!! Hahahahaha.

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

Similar Threads

  1. Query to Pull from Date Range & Check 2 Areas
    By esh112288 in forum Queries
    Replies: 9
    Last Post: 11-12-2013, 10:05 PM
  2. Filter Report by Optional Date Range and Combo Box
    By ARickert in forum SQL Server
    Replies: 3
    Last Post: 10-17-2012, 10:46 AM
  3. Replies: 4
    Last Post: 05-26-2012, 09:29 AM
  4. Replies: 15
    Last Post: 04-06-2012, 10:57 AM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 PM

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