Is it possible to have flexible formatting on date in filter?

[AccessIdiot]

Sorry, wasn't sure how to phrase the question but here's the situation:

I have a form that allows a user to apply a variety of filters to a report. One of the filters is a date range where user can enter either start date, or start/end date to confine the range. In the table that is bound to the report the date field is formatted mm/dd/yyyy.

In the code on the form I am using the following:

Const conJetDate = "\#mm\/dd\/yyyy\#"
'and later
If Me.txtStartDate > "" Then
        strDate = strDate & " AND ([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ")"
    End If
    
    If Me.txtEndDate > "" Then
        strDate = strDate & " AND ([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ")"
    End If

So here's my question: is it possible to change the code in such a way that the user has more flexibility with the date format? So that one could type in either 5/23/2002 to start searching from that specific date OR just 2002 to search for the entire year?

I'd love for the user to type in start date 2002 end date 2003 and get all the values between 1/1/2002 and 12/31/2003. OR type in 2002 and get all records from 1/1/2002 to the present.

Is that possible? Thanks for any help.

[nico5038]

Guess you'll need to use separate combo's to get this done.

I normally create a Year combo based on the year from the table the report is based on.

select distinct Year(ReportDate) from tblReport

Using the Format statement you can create a similar query to extract the YearMonth from the tblReport.

Creating a From and To combo will enable a swift selection, when you fill the To combo Default with the highest value from the query.

Idea ?

Nic;o)

[AccessIdiot]

Huh. You kinda lost me. :-D

[nico5038]

I gave the way how to place the combo's needed on a form.
For the Year a YearFrom and YearTo combo, for YearMonth a YearMonthFrom and a YearMonthTo and DateFrom and DateTo.

Basic idea is to use a SELECT DISTINCT to get the values from the table the report is based on.

The WHERE clause can always be constructed, depending on the used filter combo's.

The Year and Format used for the fill of the combo's is the basic way to construct the WHERE clause with a BETWEEN like:

WHERE YEAR BETWEEN cmbYearFrom and cmbYearTo

Clearer ?

Nic;o)

[AccessIdiot]

A bit. I'd rather not use a combo though, then you have to fill it with values right?

I like have a text box where the user can enter the whole date. I'd just really like them to be able to enter 1990 to 1991 instead of having to type out 1/1/1990 to 12/31/1991.

Does that make sense?

Is there any way in the code to say something like if # characters entered is less than 8 characters (x/x/xxxx) then find all records that match the year (*/*/xxxx).

Is something like that possible without getting all crazy complicated?

[nico5038]

You can when you make it a plain text box instead of using a date formatted field.
The downside is however:
1) You need to check for valid input for every combination allowed
2) You need to check or the entered date is OK and fits your data.
3) You can't detect or 03/04/2010 is March the 4th or April the 3rd entered the wrong way...

That's why I always present the users data available in the application. Saves the testing and they know that only available Year's (or months and dates) are selectable and thus they'll never get an empty report when entering a wrong date....

Nic;o)

[AccessIdiot]

Ah, okay. Well the text boxes are formatted for short date and the field it's drawing from is short date so I guess I'll have to be happy with 1/1/2002 to 12/31/2002 for 2002.

Thanks!

[nico5038]

Or you could add a separate combo for the years :-)

Success with the application !

Nic;o)

[AccessIdiot]

Ahhhhhhhhhh. Light bulb moment. Now I see what you were getting at. Sorry to be so dense - I'll check in with the client to see if they want something like that. We're talking 60+ years of data, so they might like the drop down, or they might not want to touch it. :-)

Cheers!

[nico5038]

Always pleased to see Light bulb moments, as that's why I help here :-)

Last tip, sort the year combo descending, thus having the most recent years on top.

Nic;o)

[NeoPa]

There's a lot of good sense in Nico's proposal. I will try to deal with the question from the perspective of data typed into the TextBox alone though.

Assuming TextBoxes called [txtStartDate] and [txtEndDate] and also a CommandButton called [cmdGo], we could try some code to filter out what we need. Also, importantly, that we are filtering dates but without any time contingents. 31/8/2010 is numerically less than 31/8/2010 01:00:00, even though it represents a whole day, most of which is after 1 O'Clock in the morning.

Private Sub cmdGo_Click()
  Dim strFilter As String, strEnd As String
  Dim intCount As Integer
  Dim datStart As Date, datEnd as Date

  With Me
    'If End Date but no Start Date assume operator error and switch
    If IsNull(.txtStartDate) Then 
      .txtStartDate = .txtEndDate
      .txtEndDate = Null
    End If
    If Not IsNull(.txtStartDate) Then
      datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, _
                           "1/1/", _
                           "") & .txtStartDate)
      'Now datStart set, proceed to datEnd
      strEnd = Nz(.txtEndDate, .txtStartDate)
      intCount = UBound(Split(strEnd, "/"))
      If intCount = 0 Then strEnd = "12/" & strEnd
      datEnd = CDate(strEnd)
      If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
      'Now Dates are determined, formulate into Filter string
      strFilter = "([Field Obs Date]" & _
                  IIf(datStart = datEnd, "=%S", " Between %S And %E)")
      strFilter = Replace(strFilter, "%S", Format(datStart, conJetDate)
      strFilter = Replace(strFilter, "%E", Format(datEnd, conJetDate)
    End If
    'Continue from here.  Filter is set (or blank if no dates entered)
  End With
End Sub

I'm afraid this hasn't been tested, so please let me know if you find any problems with it.

[AccessIdiot]

A,

I'm trying to incorporate the code into my existing code so now it looks like this for the "Apply Filter" button I have:

Private Sub cmdApplyFilter_Click()
'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    
    Dim strFilter As String                  'The criteria string.
    Dim strEnd As String
    Dim strType As String
    Dim strIsland As String
    Dim strDate As String
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Dim intCount As Integer
    Dim datStart As Date, datEnd As Date
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If

'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice
'and then construct the appropriate SQL:
    
    If Me.cboType.Value > "" Then
        strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
    End If

    If Me.cboIsland.Value > "" Then
        strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
    End If
    
    'Date field. Use the format string to add the # delimiters and get the right international format.
    If Me.txtStartDate > "" Then
        strDate = strDate & " AND ([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ")"
    End If
    
    If Me.txtEndDate > "" Then
        strDate = strDate & " AND ([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ")"
    End If
    With Me
        'If End Date but no Start Date assume operator error and switch
    If IsNull(.txtStartDate) Then
      .txtStartDate = .txtEndDate
      .txtEndDate = Null
    End If
    If Not IsNull(.txtStartDate) Then
      datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, _
                           "1/1/", _
                           "") & .txtStartDate)
      'Now datStart set, proceed to datEnd
      strEnd = Nz(.txtEndDate, .txtStartDate)
      intCount = UBound(Split(strEnd, "/"))
      If intCount = 0 Then strEnd = "12/" & strEnd
      datEnd = CDate(strEnd)
      If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
      'Now Dates are determined, formulate into Filter string
      strFilter = "([Field Obs Date]" & IIf(datStart = datEnd, "=%S", " Between %S And %E)")
      strFilter = Replace(strFilter, "%S", Format(datStart, conJetDate))
      strFilter = Replace(strFilter, "%E", Format(datEnd, conJetDate))
    End If
    'Continue from here.  Filter is set (or blank if no dates entered)
  End With
    

'Combine the criteria to form a WHERE clause for the filter:
    strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland & " AND [Field Obs Date] " & strDate
    If strFilter = "" Then
        MsgBox "No search criteria stated"
    End If
    Debug.Print strFilter
    With Reports![rpt_Report]
         .Filter = strFilter
         .FilterOn = True
    End With
End Sub

When I type in "2006" into the txtStart box I get an error message that the value entered isn't formatted correctly. I do have the text box formatted to be a short date. If I remove the formatting and type in "2006" nothing happens (no error message) when I click the Apply Filter button (no filtering of the report is applied). If I type in 1/1/2006 then it does filter correctly. So obviously I'm not formatting your code correctly into my current code. I tried changing "strFilter" to "strDate" to use in final output string but I get another error message that [Field Obs Date] is an undefined function.

Thoughts?

[NeoPa]

I'm just shooting off home now M. I'll go through it in more detail this evening when I get in.

[AccessIdiot]

No worries, thanks for any help.

(Funny, I'm just starting my day here).

[NeoPa]

Ah, but you're west Coast, and I'm Easter than East :D