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

[AccessIdiot]

Yep yep, with funny accents and all. :-)

[NeoPa]

I'd start by saying to put conJetDate as a Public Const in a code module if you have one. Otherwise take it out of any procedures and make it private to the Form module. Only the hashes (#) need the backslashes (\) to be recognised correctly by the way. Most controls have the .Value as the default property, and as such this needn't be specified. Otherwise, you seem to be doing some of the filtering twice - once the old way and again the new way, at least that's what it seems like.

Private Const conJetDate = "\#mm/dd/yyyy\#"   'The format expected for dates in a JET query string.
 
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 start of each condition so you can easily add more search boxes; _
                        we remove the leading " 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); _
                        Month or Year   = all dates within said month/year.
 
    Dim strFilter As String                  'The criteria string.
    Dim strEnd As String, 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
 
' 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:
 
    With Me
        If .cboType > "" Then _
            strFilter = strFilter & " AND ([Nest Location]=" & .cboType & ")"
        If .cboIsland > "" Then _
            strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
        '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
          strDate = "([Field Obs Date]" & IIf(datStart = datEnd, "=%S", " Between %S And %E)")
          strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
          strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
        End If
        'Lose the first " AND " and add the Date filter
        strFilter = Mid(strFilter & strDate, 6)
    End With
 
'Combine the criteria to form a WHERE clause for the filter:
    Debug.Print strFilter
    With Reports![rpt_Report]
         .Filter = strFilter
         .FilterOn = (strFilter > "")
    End With
End Sub

NB. The date filtering doesn't quite match the comments. A single date selects just that date. Let me know how you want it to work.

[AccessIdiot]

Can't test it because it doesn't like this:

Private Const conJetDate = "\#mm/dd/yyyy\#"   'The format expected for dates in a JET query string.

and I don't know how to fix it.

What I really want:
See attached for the form and the report. The form basically acts as a filter for the report. All are optional. One of the filters is for date. It would be GREAT if the user had the ability to enter either JUST a start date or put in a full range. It would also be great if the user could enter a year, or start and end years, without having to put in the month and day.

So the form would be flexible enough for the following scenarios:

1) User enters "5/23/2006" and the report would show all records from 5/23/2006 to the present.
2) User enters "5/23/2006" in start date and "10/6/2008" in end date and the report would show all records between those two dates.
3) User enters "2006" and report would show all records from 2006 to present.
4) User enters "2006" in start date and "2008" in end date and the report would show all records between (and including) all those dates (i.e. 3 years worth: 2006, 2007, 2008).

Does that help? It'd be great to do this with just the two text boxes, so that all the complexity is behind the scenes, but I understand if this is too difficult.

I gotta say I didn't understand about half the code you presented. There are a lot of functions there that I've never seen or heard of.

*edit* why is the attachment manager shrinking my jpg down to nothing?!

[NeoPa]

I'll look at this in more detail later as it's getting near my bedtime.

For now, just put the conJetDate back as it was before. I forgot that form modules don't allow constants to be defined in the public area (outside of all procedures).

I may take you through doing it properly in a standard code module sometime, but for now revert it to what you had before and start testing the results. The rest of the code should be good, even if it's not an exact match to your requirements as yet. It'll give a good understanding of the sort of things that can be done.

PS. The pics are mucked up because the forum software doesn't handle them very well at this time. The settings are set up to reduce them too much to be a lot of use. That will probably change soon, but not soon enough to help here I'm afraid.

[NeoPa]

I've just checked the details of the Const line. It seems that constants are allowed within object modules (modules associated with forms or reports), as long as they are defined as Private rather than as Public.

Can you change the code slightly for me (as it is missing the type specification) and tell me if it works. If not, please post the error number and message you see for me.

The revised lines should be :

'The format expected for dates in a JET query string.
Private Const conJetDate As String = "\#m/d/yyyy\#"

[NeoPa]

I will need to rearrange the code a small amount to :
Allow an End date without a Start date.
Treat a Start date or End date, on their own, as starting or ending a period rather than a single date.

Another point to mention is that you have two table fields ([Nest Location] and [Island Name]) which both give the impression of being string fields, yet the SQL treats them as numbers. [cboType] and [cboIsland] are similarly used in this and the returned values of these ComboBoxes should match the type of the field and the format of the SQL. It will only work correctly, as things stand, if all these items are numeric.

'The format expected for dates in a JET query string.
Private Const conJetDate As String = "\#m/d/yyyy\#"
 
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 start of each condition so you can easily add more search boxes; _
                        we remove the leading " 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); _
                        Month or Year   = For Start date the first day of the period
                                          For End date the last day of the period.
 
    Dim strFilter As String                 'The criteria string.
    Dim strEnd As String, 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
 
' 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:
    With Me
        If .cboType > "" Then _
            strFilter = " AND ([Nest Location]=" & .cboType & ")"
        If .cboIsland > "" Then _
            strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
        'Handle Start Date
        If IsNull(.txtStartDate) Then
            datStart = #1/1/1900#
        Else
            datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
        End If
        'Now datStart set, proceed to datEnd
        strEnd = Nz(.txtEndDate, "31 Dec 9999")
        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
        strDate = "([Field Obs Date] Between %S And %E)")
        strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
        strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
        'Lose the first " AND " and add the Date filter
        strFilter = Mid(strFilter & strDate, 6)
    End With
 
'Combine the criteria to form a WHERE clause for the filter:
    Debug.Print strFilter
    With Reports![rpt_Report]
        .Filter = strFilter
        .FilterOn = (strFilter > "")
    End With
End Sub

[AccessIdiot]

Thanks A,

Run-time error '13'" Type mismatch

On this line:

datEnd = CDate(strEnd)

[NeoPa]

Can you post the value of .txtEndDate when you get this error (on line #44 I assume) please M.

If you're really quick I may get to it tonight, but I'm nearly ready to hit the big button ;)

[AccessIdiot]

I am entering only a start date and leaving the end date blank to see if the report will filter from just one date like "1960". If I put in a start and end date (1960, 2010) then I get an error message:

"The value you entered isn't valid for this field."

*edit* If I put in 1960 (start date) and 1/1/2010 (end date) I get the following:
"Run-time error '3075': Syntax error (missing operator) in query expression 'ld Obs Date] Between #1/1/1960# And #1/1/2010#)'."

So, messages all over the board. Seems like the last would work if part of the field name wasn't getting cut off?

[NeoPa]

AccessIdiot: If I put in a start and end date (1960, 2010) then I get an error message:

"The value you entered isn't valid for this field."

Try changing line #41 to :

strEnd = Nz(.txtEndDate, "12/31/9999")

[NeoPa]

AccessIdiot: *edit* If I put in 1960 (start date) and 1/1/2010 (end date) I get the following:
"Run-time error '3075': Syntax error (missing operator) in query expression 'ld Obs Date] Between #1/1/1960# And #1/1/2010#)'."

So, messages all over the board. Seems like the last would work if part of the field name wasn't getting cut off?

While you're about it you could also change line #61 to :

strFilter = " AND ([Field Obs Date] Between %S And %E)")

[mseo]

@AccessIdiot
you can use a function like this

Function GetDateFilter(dtDate As Date) As String
    ' Date filters must be in MM/DD/YYYY format
    GetDateFilter = Format(dtDate, "\#YYYY\#")
End Function

and call the function:

  If IsDate(Me.Datefrom) Then
       strWhere = strWhere & " AND " & "your table or query name.[date field] >= " & GetDateFilter(Me.Datefrom)
    End If
 
 If IsDate(Me.DateTo) Then
        strWhere = strWhere & " AND " & "your table or query name.[Date field] <= " & GetDateFilter(Me.DateTo)
    End If
 

hope this helps

[NeoPa]

If the last two changes don't fix it up for you M, then you may want to consider letting me have a copy of the db. It's a fair chunk of code to do on the blind (I overlooked two important details already when I made the last change).

If you're not comfortable attaching a copy publicly then you can email it to me. My address hasn't changed, but if you don't have it to hand I'd be happy to send it on again (in a PM from here, but I think I may be able to email from home. I think I still have your email there - assuming that hasn't changed of course).

[AccessIdiot]

Getting close! Everything works GREAT except for the end date, and it only barks if I put in just the year. If I put in m/d/yyyy for an end date it's fine. If I put in yyyy for an end date I get:
"the value you entered isn't valid for this field."

Do I need the # signs again around the end date?

By the way I have no trouble with putting in yyyy for the start date and leaving the end date blank.

mseo thanks so much for the suggestion. I'd like to try that if I can't get NeoPa's version to work. I feel like we're really close though. (And I say "we" so liberally . . .).

[mseo]

@AccessIdiot
you're welcome
I am really glad that you found the solution for your issue

[NeoPa]

OK. Line #43 should possibly be changed to :

If intCount = 0 Then
    strEnd = "12/" & strEnd
    intCount = 1
End If

If this doesn't resolve the issue, consider sending me a copy of the database. It's a bit easier when I can look and see exactly what's happening. Anyway, give this a go.

[NeoPa]

AccessIdiot: Getting close! Everything works GREAT except for the end date, and it only barks if I put in just the year. If I put in m/d/yyyy for an end date it's fine. If I put in yyyy for an end date I get:
"the value you entered isn't valid for this field."

This is a bit unclear to me. This looks like it may be control or field validation, which is entirely unconnected with the code. This sort of hunting for problems that are within the design of the form is very difficult to effect remotely via a third party. If the code change doesn't fix it then you'll need to look at the design of the field and control. Particularly with a view to checking Validation properties.

The code change should be kept anyway. The code wasn't perfect before and this was a minor, but significant, problem anyway (even if, as I suspect, it doesn't resolve this particular issue).

[AccessIdiot]

Hmmm, strange. Now all of a sudden I get a compile error "End With without With" when clearly it says "With Me" in the function?

[NeoPa]

AccessIdiot: Hmmm, strange. Now all of a sudden I get a compile error "End With without With" when clearly it says "With Me" in the function?

This is often due to one or other being within an If construct which isn't properly terminated (You can't have a conditional End With), but I expect you're sending the db shortly so I'll look at it for myself. If it's soon I can manage it tonight. Otherwise it'll likely be on Thursday I expect, as I'm out tomorrow.

[AccessIdiot]

Sent! thanks. I looked for other with statements but only saw the one. Very mysterious.

[NeoPa]

As I may not get to look at your database until Thursday now (that's at home you see & I'm out tonight), would it be possible just to post the whole code module for the form in question. I may get a few minutes spare to scan through it and may pick up the problem visually. Of course, if I don't manage to get anywhere I'll still have the db to fall back on tomorrow night.

[AccessIdiot]

Sorry for the delay - got in about half an hour ago but the site's been down. Here it is:

Option Compare Database
 
Private Const conJetDate As String = "\#m\/d/yyyy\#"   'The format expected for dates in a JET query string.
 
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 start of each condition so you can easily add more search boxes; _
                        we remove the leading " 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); _
                        Month or Year   = For Start date the first day of the period; _
                                          For End date the last day of the period.
 
    Dim strFilter As String                 'The criteria string.
    Dim strEnd As String, 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
 
' 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:
    With Me
        If .cboType > "" Then
            strFilter = " AND ([Nest Location]=" & .cboType & ")"
        End If
        If .cboIsland > "" Then
            strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
        End If
        'Handle Start Date
        If IsNull(.txtStartDate) Then
            datStart = #1/1/1900#
        Else
            datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
        End If
        'Now datStart set, proceed to datEnd
        strEnd = Nz(.txtEndDate, "12/31/9999")
        intCount = UBound(Split(strEnd, "/"))
        If intCount = 0 Then
            strEnd = "12/" & strEnd
            intCount = 1
        End If
        datEnd = CDate(strEnd)
        If intCount = 1 Then
            datEnd = DateAdd("m", 1, datEnd) - 1
        End If
        'Now Dates are determined, formulate into Filter string
        strDate = " AND ([Field Obs Date] Between %S And %E)"
        strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
        strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
        'Lose the first " AND " and add the Date filter
        strFilter = Mid(strFilter & strDate, 6)
    End With
 
'Combine the criteria to form a WHERE clause for the filter:
    Debug.Print strFilter
    With Reports![rpt_Report]
        .Filter = strFilter
        .FilterOn = (strFilter > "")
    End With
End Sub
 
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_Report].FilterOn = False
     Me.cboType.Value = Null
     Me.cboIsland.Value = Null
     Me.txtEndDate = Null
     Me.txtStartDate = Null
End Sub
 
Private Sub Form_Load()
    DoCmd.OpenReport "rpt_Report", acViewReport
End Sub
 

[NeoPa]

Thanks M. I, too, have been waiting for the site to come back up. I IMed the owner a while ago but I'm not sure he got the message.

[NeoPa]

I couldn't find a problem with your version of the code (posted). I created a dummy form with the same controls on, and the code compiled ok.

Is your error occurring at compile time?

Is it happening only when you run it?

Either way, can you give any indication as to the line associated with the message.

[AccessIdiot]

EDIT. I AM an Access Idiot. I had a short date format applied to the end date text box. Once I took that off it works JUST FINE. *smacks forhead*.

Sorry for all the trouble - sometimes it's the little things.

But since we're here . . . could you please comment or explain your sections of the code to me? Specifically lines 41 through 59.

[NeoPa]

AccessIdiot:

Option Compare Database
 
Private Const conJetDate As String = "\#m\/d/yyyy\#"   'The format expected for dates in a JET query string.
 
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 start of each condition so you can easily add more search boxes; _
                        we remove the leading " 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); _
                        Month or Year   = For Start date the first day of the period; _
                                          For End date the last day of the period.
 
    Dim strFilter As String                 'The criteria string.
    Dim strEnd As String, 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
 
' 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:
    With Me
        If .cboType > "" Then
            strFilter = " AND ([Nest Location]=" & .cboType & ")"
        End If
        If .cboIsland > "" Then
            strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
        End If
        'Handle Start Date
        If IsNull(.txtStartDate) Then
            datStart = #1/1/1900#
        Else
            datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
        End If
        'Now datStart set, proceed to datEnd
        strEnd = Nz(.txtEndDate, "12/31/9999")
        intCount = UBound(Split(strEnd, "/"))
        If intCount = 0 Then
            strEnd = "12/" & strEnd
            intCount = 1
        End If
        datEnd = CDate(strEnd)
        If intCount = 1 Then
            datEnd = DateAdd("m", 1, datEnd) - 1
        End If
        'Now Dates are determined, formulate into Filter string
        strDate = " AND ([Field Obs Date] Between %S And %E)"
        strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
        strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
        'Lose the first " AND " and add the Date filter
        strFilter = Mid(strFilter & strDate, 6)
    End With
 
'Combine the criteria to form a WHERE clause for the filter:
    Debug.Print strFilter
    With Reports![rpt_Report]
        .Filter = strFilter
        .FilterOn = (strFilter > "")
    End With
End Sub
 
 
 
Private Sub cmdRemoveFilter_Click()
     On Error Resume Next
     Reports![rpt_Report].FilterOn = False
     Me.cboType.Value = Null
     Me.cboIsland.Value = Null
     Me.txtEndDate = Null
     Me.txtStartDate = Null
End Sub
 
Private Sub Form_Load()
    DoCmd.OpenReport "rpt_Report", acViewReport
End Sub

With reference to the code you posted in #37 :
Lines #38-#42
If there is nothing enetered for the txtStartDate then use 31 December 1900.
Otherwise look for the string "/" within txtStartDate. If it exists (not a simple year value) then we can treat it as a date and leave it as it is. Otherwise we prepend the string "1/1/", thus turning it into a recognisable date value. Whichever of these date values results is then translated using CDate() into an actual date value (rather than a string of viable date format).

Line #44
Save the value of txtEndDate in strEnd, but if there is no value then use 31 December 9999 instead.

Line #45
Split(strEnd, "/") produces an array of items from the strEnd separated by the "/" string. "12/31/9999" produces an array of elements :-
"12"
"31"
"9999"
"12/1985" on the other hand would produce :-
"12"
"1985"
UBound of an array returns the upper bound. As arrays naturally start at element 0 the value returned reflects the number of "/"s found. Two for the first example and one for the second.

Thus, intCount is set to the count of "/"s in strEnd.

Lines #46-#49
If intCount=0 then a simple year was entered (as no "/"s were found). In that case we prepend "12/" to turn it into a month date, then update intCount to reflect the change. Month dates in that format always assume the first when translated to dates.

Debug.Print Format(#12/1999#, "m/d/yyyy")

produces "12/1/1999"

Line #50
Set datEnd to reflect the date value of strEnd.

Lines #51-#53
If the value is a Month date then convert the date to the last date of the month. Essentially add a month (DateAdd("m", 1, datEnd)) then subtract a day (- 1) to get the last day of the month. Remember, if just a year was entered then this has already been changed to December of that year (Lines #46-#49). We are now left with a full date regardless of how the data was entered.

Line #55
This is the template of the string we wish to add to strFilter. "%S" and "%E" will later be changed (leaving the rest of the string intact) to the actual values we've worked out.

Lines #56-#57
Using the Replace() function, we replace "%S" and "%E" with a string which is made from the two dates formatted using conJetDate. We are left with something like :

 AND ([Field Obs Date] Between #1/1/2010# And #12/31/2010#)

Line #59
We now set the filter to whatever was in the filter before, with the strDate filtering appended, but the first five characters removed from the start of this result (Mid(X, 6) essentially has that effect). Remember, whatever was added to the filter always started with " AND ", which is only required between the various criteria. We are thus guaranteed such a string at the start that needs to be removed before use. We are now left with exactly the filter that we're after.

I hope that explains the whole code clearly.

[AccessIdiot]

Excellent explanation. I have just a few questions.

1) Line 53

datEnd = DateAdd("m", 1, datEnd) - 1

I understand the purpose but why "m"?

2) Line 56-58 - what is the purpose of using %S and %E if you end up replacing them?

[NeoPa]

AccessIdiot: Excellent explanation.

Thank you :)

AccessIdiot: 1) Line 53

datEnd = DateAdd("m", 1, datEnd) - 1

I understand the purpose but why "m"?

"m" indicates to proceed forwards (or backwards for negative values of the second parameter) in steps of a month (If you press F1 for context-sensitive help when the cursor is on the word DateAdd in your module you'll get the full breakdown of the function and all its parameters).

Consider you have entered a date of "12/2009". This will be treated as #12/1/2009# when converted to a date. When the DateAdd() has been applied we have a result of #1/1/2010#. After 1 day is subtracted from this we are left with #12/31/2009#. Exactly what we hoped for when we entered "12/2009".

AccessIdiot: 2) Line 56-58 - what is the purpose of using %S and %E if you end up replacing them?

They are placeholders in the template. These two strings specify where within the template string the actual (string) values for the dates should end up.

An alternative would be to build up the string bit by bit, but I prefer this more explicit and readable approach. I have a function I use called MultiReplace() that extends this concept somewhat and allows me to pass multiple pairs of parameters after the initial template string. That way I can say simply :

strDate = MultiReplace(" AND ([Field Obs Date] Between %S And %E)", _
                       "%S", Format(datStart, conJetDate), _
                       "%E", Format(datEnd, conJetDate))

The alternative would be :

strDate = " AND ([Field Obs Date] Between " & _
          Format(datStart, conJetDate) & " And " & _
          Format(datEnd, conJetDate) & ")"

I find with this approach that I need to decipher the whole command before I can even appreciate what is being attempted. Using replace, it is easy(er generally) to see what is going on simply by looking at the template string.

[AccessIdiot]

Very cool, and way beyond my code writing abilities but hopefully I can find another project to practice some of these techniques on.

Thanks again NeoPa, I've really enjoyed this one. :-)

[NeoPa]

AccessIdiot: Thanks again NeoPa, I've really enjoyed this one. :-)

Always fun M :)

Feel free to PM me if you post another and would like me to take a look at it. Normally this is against the rules, but it's ok if you have the person's permission.

[AccessIdiot]

I appreciate that, and may do so as the next thing I will be researching is levels of access to different parts of the database.

Cheers!