By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,481 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,481 IT Pros & Developers. It's quick & easy.

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

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1. Const conJetDate = "\#mm\/dd\/yyyy\#"
  2. 'and later
  3. If Me.txtStartDate > "" Then
  4.         strDate = strDate & " AND ([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ")"
  5.     End If
  6.  
  7.     If Me.txtEndDate > "" Then
  8.         strDate = strDate & " AND ([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ")"
  9.     End If
  10.  
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.
Jul 14 '10 #1

✓ answered by NeoPa

AccessIdiot:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Const conJetDate As String = "\#m\/d/yyyy\#"   'The format expected for dates in a JET query string.
  4.  
  5. Private Sub cmdApplyFilter_Click()
  6. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  7.     'Notes:     1. We tack " AND " on the start of each condition so you can easily add more search boxes; _
  8.                         we remove the leading " AND " at the end.
  9.     '           2. The date range works like this: _
  10.                         Both dates      = only dates between (both inclusive); _
  11.                         Start date only = all dates from this one onwards; _
  12.                         End date only   = all dates up to (and including this one); _
  13.                         Month or Year   = For Start date the first day of the period; _
  14.                                           For End date the last day of the period.
  15.  
  16.     Dim strFilter As String                 'The criteria string.
  17.     Dim strEnd As String, strDate As String
  18.     Dim lngLen As Long                      'Length of the criteria string to append to.
  19.     Dim intCount As Integer
  20.     Dim datStart As Date, datEnd As Date
  21.  
  22. ' Check that the report is open
  23.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  24.         MsgBox "You must open the report first."
  25.         Exit Sub
  26.     End If
  27.  
  28. 'If a combo box is empty its value is Null so I can use an If Statement to check
  29. 'whether or not the user made a choice and then construct the appropriate SQL:
  30.     With Me
  31.         If .cboType > "" Then
  32.             strFilter = " AND ([Nest Location]=" & .cboType & ")"
  33.         End If
  34.         If .cboIsland > "" Then
  35.             strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
  36.         End If
  37.         'Handle Start Date
  38.         If IsNull(.txtStartDate) Then
  39.             datStart = #1/1/1900#
  40.         Else
  41.             datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
  42.         End If
  43.         'Now datStart set, proceed to datEnd
  44.         strEnd = Nz(.txtEndDate, "12/31/9999")
  45.         intCount = UBound(Split(strEnd, "/"))
  46.         If intCount = 0 Then
  47.             strEnd = "12/" & strEnd
  48.             intCount = 1
  49.         End If
  50.         datEnd = CDate(strEnd)
  51.         If intCount = 1 Then
  52.             datEnd = DateAdd("m", 1, datEnd) - 1
  53.         End If
  54.         'Now Dates are determined, formulate into Filter string
  55.         strDate = " AND ([Field Obs Date] Between %S And %E)"
  56.         strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
  57.         strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
  58.         'Lose the first " AND " and add the Date filter
  59.         strFilter = Mid(strFilter & strDate, 6)
  60.     End With
  61.  
  62. 'Combine the criteria to form a WHERE clause for the filter:
  63.     Debug.Print strFilter
  64.     With Reports![rpt_Report]
  65.         .Filter = strFilter
  66.         .FilterOn = (strFilter > "")
  67.     End With
  68. End Sub
  69.  
  70.  
  71.  
  72. Private Sub cmdRemoveFilter_Click()
  73.      On Error Resume Next
  74.      Reports![rpt_Report].FilterOn = False
  75.      Me.cboType.Value = Null
  76.      Me.cboIsland.Value = Null
  77.      Me.txtEndDate = Null
  78.      Me.txtStartDate = Null
  79. End Sub
  80.  
  81. Private Sub Form_Load()
  82.     DoCmd.OpenReport "rpt_Report", acViewReport
  83. 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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1.  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.

Share this Question
Share on Google+
45 Replies


nico5038
Expert 2.5K+
P: 3,072
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.
Expand|Select|Wrap|Line Numbers
  1. select distinct Year(ReportDate) from tblReport
  2.  
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)
Jul 14 '10 #2

AccessIdiot
100+
P: 493
Huh. You kinda lost me. :-D
Jul 14 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. WHERE YEAR BETWEEN cmbYearFrom and cmbYearTo
  2.  
Clearer ?

Nic;o)
Jul 14 '10 #4

AccessIdiot
100+
P: 493
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?
Jul 14 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 14 '10 #6

AccessIdiot
100+
P: 493
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!
Jul 14 '10 #7

nico5038
Expert 2.5K+
P: 3,072
Or you could add a separate combo for the years :-)

Success with the application !

Nic;o)
Jul 14 '10 #8

AccessIdiot
100+
P: 493
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!
Jul 14 '10 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 15 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGo_Click()
  2.   Dim strFilter As String, strEnd As String
  3.   Dim intCount As Integer
  4.   Dim datStart As Date, datEnd as Date
  5.  
  6.   With Me
  7.     'If End Date but no Start Date assume operator error and switch
  8.     If IsNull(.txtStartDate) Then 
  9.       .txtStartDate = .txtEndDate
  10.       .txtEndDate = Null
  11.     End If
  12.     If Not IsNull(.txtStartDate) Then
  13.       datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, _
  14.                            "1/1/", _
  15.                            "") & .txtStartDate)
  16.       'Now datStart set, proceed to datEnd
  17.       strEnd = Nz(.txtEndDate, .txtStartDate)
  18.       intCount = UBound(Split(strEnd, "/"))
  19.       If intCount = 0 Then strEnd = "12/" & strEnd
  20.       datEnd = CDate(strEnd)
  21.       If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
  22.       'Now Dates are determined, formulate into Filter string
  23.       strFilter = "([Field Obs Date]" & _
  24.                   IIf(datStart = datEnd, "=%S", " Between %S And %E)")
  25.       strFilter = Replace(strFilter, "%S", Format(datStart, conJetDate)
  26.       strFilter = Replace(strFilter, "%E", Format(datEnd, conJetDate)
  27.     End If
  28.     'Continue from here.  Filter is set (or blank if no dates entered)
  29.   End With
  30. End Sub
I'm afraid this hasn't been tested, so please let me know if you find any problems with it.
Jul 15 '10 #11

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdApplyFilter_Click()
  2. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  3.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  4.                         we remove the trailing " AND " at the end.
  5.     '           2. The date range works like this: _
  6.                         Both dates      = only dates between (both inclusive. _
  7.                         Start date only = all dates from this one onwards; _
  8.                         End date only   = all dates up to (and including this one).
  9.  
  10.     Dim strFilter As String                  'The criteria string.
  11.     Dim strEnd As String
  12.     Dim strType As String
  13.     Dim strIsland As String
  14.     Dim strDate As String
  15.     Dim lngLen As Long                      'Length of the criteria string to append to.
  16.     Dim intCount As Integer
  17.     Dim datStart As Date, datEnd As Date
  18.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  19.  
  20. ' Check that the report is open
  21.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  22.         MsgBox "You must open the report first."
  23.         Exit Sub
  24.     End If
  25.  
  26. '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
  27. 'and then construct the appropriate SQL:
  28.  
  29.     If Me.cboType.Value > "" Then
  30.         strType = strType & " AND ([Nest Location]=" & Me.cboType.Value & ")"
  31.     End If
  32.  
  33.     If Me.cboIsland.Value > "" Then
  34.         strIsland = strIsland & " AND ([Island Name]=" & Me.cboIsland.Value & ")"
  35.     End If
  36.  
  37.     'Date field. Use the format string to add the # delimiters and get the right international format.
  38.     If Me.txtStartDate > "" Then
  39.         strDate = strDate & " AND ([Field Obs Date] >= " & Format(Me.txtStartDate, conJetDate) & ")"
  40.     End If
  41.  
  42.     If Me.txtEndDate > "" Then
  43.         strDate = strDate & " AND ([Field Obs Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ")"
  44.     End If
  45.     With Me
  46.         'If End Date but no Start Date assume operator error and switch
  47.     If IsNull(.txtStartDate) Then
  48.       .txtStartDate = .txtEndDate
  49.       .txtEndDate = Null
  50.     End If
  51.     If Not IsNull(.txtStartDate) Then
  52.       datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, _
  53.                            "1/1/", _
  54.                            "") & .txtStartDate)
  55.       'Now datStart set, proceed to datEnd
  56.       strEnd = Nz(.txtEndDate, .txtStartDate)
  57.       intCount = UBound(Split(strEnd, "/"))
  58.       If intCount = 0 Then strEnd = "12/" & strEnd
  59.       datEnd = CDate(strEnd)
  60.       If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
  61.       'Now Dates are determined, formulate into Filter string
  62.       strFilter = "([Field Obs Date]" & IIf(datStart = datEnd, "=%S", " Between %S And %E)")
  63.       strFilter = Replace(strFilter, "%S", Format(datStart, conJetDate))
  64.       strFilter = Replace(strFilter, "%E", Format(datEnd, conJetDate))
  65.     End If
  66.     'Continue from here.  Filter is set (or blank if no dates entered)
  67.   End With
  68.  
  69.  
  70. 'Combine the criteria to form a WHERE clause for the filter:
  71.     strFilter = "[Nest Location] " & strType & " AND [Island Name] " & strIsland & " AND [Field Obs Date] " & strDate
  72.     If strFilter = "" Then
  73.         MsgBox "No search criteria stated"
  74.     End If
  75.     Debug.Print strFilter
  76.     With Reports![rpt_Report]
  77.          .Filter = strFilter
  78.          .FilterOn = True
  79.     End With
  80. 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?
Jul 15 '10 #12

NeoPa
Expert Mod 15k+
P: 31,768
I'm just shooting off home now M. I'll go through it in more detail this evening when I get in.
Jul 15 '10 #13

AccessIdiot
100+
P: 493
No worries, thanks for any help.

(Funny, I'm just starting my day here).
Jul 15 '10 #14

NeoPa
Expert Mod 15k+
P: 31,768
Ah, but you're west Coast, and I'm Easter than East :D
Jul 15 '10 #15

AccessIdiot
100+
P: 493
Yep yep, with funny accents and all. :-)
Jul 15 '10 #16

NeoPa
Expert Mod 15k+
P: 31,768
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.
Expand|Select|Wrap|Line Numbers
  1. Private Const conJetDate = "\#mm/dd/yyyy\#"   'The format expected for dates in a JET query string.
  2.  
  3. Private Sub cmdApplyFilter_Click()
  4. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  5.     'Notes:     1. We tack " AND " on the start of each condition so you can easily add more search boxes; _
  6.                         we remove the leading " AND " at the end.
  7.     '           2. The date range works like this: _
  8.                         Both dates      = only dates between (both inclusive. _
  9.                         Start date only = all dates from this one onwards; _
  10.                         End date only   = all dates up to (and including this one); _
  11.                         Month or Year   = all dates within said month/year.
  12.  
  13.     Dim strFilter As String                  'The criteria string.
  14.     Dim strEnd As String, strDate As String
  15.     Dim lngLen As Long                      'Length of the criteria string to append to.
  16.     Dim intCount As Integer
  17.     Dim datStart As Date, datEnd As Date
  18.  
  19. ' Check that the report is open
  20.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  21.         MsgBox "You must open the report first."
  22.         Exit Sub
  23.     End If
  24.  
  25. '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
  26. 'and then construct the appropriate SQL:
  27.  
  28.     With Me
  29.         If .cboType > "" Then _
  30.             strFilter = strFilter & " AND ([Nest Location]=" & .cboType & ")"
  31.         If .cboIsland > "" Then _
  32.             strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
  33.         'If End Date but no Start Date assume operator error and switch
  34.         If IsNull(.txtStartDate) Then
  35.           .txtStartDate = .txtEndDate
  36.           .txtEndDate = Null
  37.         End If
  38.         If Not IsNull(.txtStartDate) Then
  39.           datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
  40.           'Now datStart set, proceed to datEnd
  41.           strEnd = Nz(.txtEndDate, .txtStartDate)
  42.           intCount = UBound(Split(strEnd, "/"))
  43.           If intCount = 0 Then strEnd = "12/" & strEnd
  44.           datEnd = CDate(strEnd)
  45.           If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
  46.           'Now Dates are determined, formulate into Filter string
  47.           strDate = "([Field Obs Date]" & IIf(datStart = datEnd, "=%S", " Between %S And %E)")
  48.           strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
  49.           strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
  50.         End If
  51.         'Lose the first " AND " and add the Date filter
  52.         strFilter = Mid(strFilter & strDate, 6)
  53.     End With
  54.  
  55. 'Combine the criteria to form a WHERE clause for the filter:
  56.     Debug.Print strFilter
  57.     With Reports![rpt_Report]
  58.          .Filter = strFilter
  59.          .FilterOn = (strFilter > "")
  60.     End With
  61. 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.
Jul 15 '10 #17

AccessIdiot
100+
P: 493
Can't test it because it doesn't like this:
Expand|Select|Wrap|Line Numbers
  1. 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?!
Attached Images
File Type: jpg filter.jpg (21.6 KB, 272 views)
Jul 15 '10 #18

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 15 '10 #19

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. 'The format expected for dates in a JET query string.
  2. Private Const conJetDate As String = "\#m/d/yyyy\#"
Jul 16 '10 #20

NeoPa
Expert Mod 15k+
P: 31,768
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.
Expand|Select|Wrap|Line Numbers
  1. 'The format expected for dates in a JET query string.
  2. Private Const conJetDate As String = "\#m/d/yyyy\#"
  3.  
  4. Private Sub cmdApplyFilter_Click()
  5. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the start of each condition so you can easily add more search boxes; _
  7.                         we remove the leading " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive); _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one); _
  12.                         Month or Year   = For Start date the first day of the period
  13.                                           For End date the last day of the period.
  14.  
  15.     Dim strFilter As String                 'The criteria string.
  16.     Dim strEnd As String, strDate As String
  17.     Dim lngLen As Long                      'Length of the criteria string to append to.
  18.     Dim intCount As Integer
  19.     Dim datStart As Date, datEnd As Date
  20.  
  21. ' Check that the report is open
  22.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  23.         MsgBox "You must open the report first."
  24.         Exit Sub
  25.     End If
  26.  
  27. 'If a combo box is empty its value is Null so I can use an If Statement to check
  28. 'whether or not the user made a choice and then construct the appropriate SQL:
  29.     With Me
  30.         If .cboType > "" Then _
  31.             strFilter = " AND ([Nest Location]=" & .cboType & ")"
  32.         If .cboIsland > "" Then _
  33.             strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
  34.         'Handle Start Date
  35.         If IsNull(.txtStartDate) Then
  36.             datStart = #1/1/1900#
  37.         Else
  38.             datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
  39.         End If
  40.         'Now datStart set, proceed to datEnd
  41.         strEnd = Nz(.txtEndDate, "31 Dec 9999")
  42.         intCount = UBound(Split(strEnd, "/"))
  43.         If intCount = 0 Then strEnd = "12/" & strEnd
  44.         datEnd = CDate(strEnd)
  45.         If intCount = 1 Then datEnd = DateAdd("m", 1, datEnd) - 1
  46.         'Now Dates are determined, formulate into Filter string
  47.         strDate = "([Field Obs Date] Between %S And %E)")
  48.         strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
  49.         strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
  50.         'Lose the first " AND " and add the Date filter
  51.         strFilter = Mid(strFilter & strDate, 6)
  52.     End With
  53.  
  54. 'Combine the criteria to form a WHERE clause for the filter:
  55.     Debug.Print strFilter
  56.     With Reports![rpt_Report]
  57.         .Filter = strFilter
  58.         .FilterOn = (strFilter > "")
  59.     End With
  60. End Sub
Jul 16 '10 #21

AccessIdiot
100+
P: 493
Thanks A,

Run-time error '13'" Type mismatch

On this line:
Expand|Select|Wrap|Line Numbers
  1. datEnd = CDate(strEnd)
Jul 19 '10 #22

NeoPa
Expert Mod 15k+
P: 31,768
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 ;)
Jul 19 '10 #23

AccessIdiot
100+
P: 493
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?
Jul 19 '10 #24

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. strEnd = Nz(.txtEndDate, "12/31/9999")
Jul 19 '10 #25

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. strFilter = " AND ([Field Obs Date] Between %S And %E)")
Jul 19 '10 #26

mseo
100+
P: 181
@AccessIdiot
you can use a function like this
Expand|Select|Wrap|Line Numbers
  1. Function GetDateFilter(dtDate As Date) As String
  2.     ' Date filters must be in MM/DD/YYYY format
  3.     GetDateFilter = Format(dtDate, "\#YYYY\#")
  4. End Function
and call the function:
Expand|Select|Wrap|Line Numbers
  1.   If IsDate(Me.Datefrom) Then
  2.        strWhere = strWhere & " AND " & "your table or query name.[date field] >= " & GetDateFilter(Me.Datefrom)
  3.     End If
  4.  
  5.  If IsDate(Me.DateTo) Then
  6.         strWhere = strWhere & " AND " & "your table or query name.[Date field] <= " & GetDateFilter(Me.DateTo)
  7.     End If
  8.  
hope this helps
Jul 20 '10 #27

NeoPa
Expert Mod 15k+
P: 31,768
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).
Jul 20 '10 #28

AccessIdiot
100+
P: 493
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 . . .).
Jul 20 '10 #29

mseo
100+
P: 181
@AccessIdiot
you're welcome
I am really glad that you found the solution for your issue
Jul 20 '10 #30

NeoPa
Expert Mod 15k+
P: 31,768
OK. Line #43 should possibly be changed to :
Expand|Select|Wrap|Line Numbers
  1. If intCount = 0 Then
  2.     strEnd = "12/" & strEnd
  3.     intCount = 1
  4. 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.
Jul 20 '10 #31

NeoPa
Expert Mod 15k+
P: 31,768
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).
Jul 20 '10 #32

AccessIdiot
100+
P: 493
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?
Jul 20 '10 #33

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 20 '10 #34

AccessIdiot
100+
P: 493
Sent! thanks. I looked for other with statements but only saw the one. Very mysterious.
Jul 20 '10 #35

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 21 '10 #36

AccessIdiot
100+
P: 493
Sorry for the delay - got in about half an hour ago but the site's been down. Here it is:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Const conJetDate As String = "\#m\/d/yyyy\#"   'The format expected for dates in a JET query string.
  4.  
  5. Private Sub cmdApplyFilter_Click()
  6. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  7.     'Notes:     1. We tack " AND " on the start of each condition so you can easily add more search boxes; _
  8.                         we remove the leading " AND " at the end.
  9.     '           2. The date range works like this: _
  10.                         Both dates      = only dates between (both inclusive); _
  11.                         Start date only = all dates from this one onwards; _
  12.                         End date only   = all dates up to (and including this one); _
  13.                         Month or Year   = For Start date the first day of the period; _
  14.                                           For End date the last day of the period.
  15.  
  16.     Dim strFilter As String                 'The criteria string.
  17.     Dim strEnd As String, strDate As String
  18.     Dim lngLen As Long                      'Length of the criteria string to append to.
  19.     Dim intCount As Integer
  20.     Dim datStart As Date, datEnd As Date
  21.  
  22. ' Check that the report is open
  23.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  24.         MsgBox "You must open the report first."
  25.         Exit Sub
  26.     End If
  27.  
  28. 'If a combo box is empty its value is Null so I can use an If Statement to check
  29. 'whether or not the user made a choice and then construct the appropriate SQL:
  30.     With Me
  31.         If .cboType > "" Then
  32.             strFilter = " AND ([Nest Location]=" & .cboType & ")"
  33.         End If
  34.         If .cboIsland > "" Then
  35.             strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
  36.         End If
  37.         'Handle Start Date
  38.         If IsNull(.txtStartDate) Then
  39.             datStart = #1/1/1900#
  40.         Else
  41.             datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
  42.         End If
  43.         'Now datStart set, proceed to datEnd
  44.         strEnd = Nz(.txtEndDate, "12/31/9999")
  45.         intCount = UBound(Split(strEnd, "/"))
  46.         If intCount = 0 Then
  47.             strEnd = "12/" & strEnd
  48.             intCount = 1
  49.         End If
  50.         datEnd = CDate(strEnd)
  51.         If intCount = 1 Then
  52.             datEnd = DateAdd("m", 1, datEnd) - 1
  53.         End If
  54.         'Now Dates are determined, formulate into Filter string
  55.         strDate = " AND ([Field Obs Date] Between %S And %E)"
  56.         strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
  57.         strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
  58.         'Lose the first " AND " and add the Date filter
  59.         strFilter = Mid(strFilter & strDate, 6)
  60.     End With
  61.  
  62. 'Combine the criteria to form a WHERE clause for the filter:
  63.     Debug.Print strFilter
  64.     With Reports![rpt_Report]
  65.         .Filter = strFilter
  66.         .FilterOn = (strFilter > "")
  67.     End With
  68. End Sub
  69.  
  70.  
  71.  
  72. Private Sub cmdRemoveFilter_Click()
  73.      On Error Resume Next
  74.      Reports![rpt_Report].FilterOn = False
  75.      Me.cboType.Value = Null
  76.      Me.cboIsland.Value = Null
  77.      Me.txtEndDate = Null
  78.      Me.txtStartDate = Null
  79. End Sub
  80.  
  81. Private Sub Form_Load()
  82.     DoCmd.OpenReport "rpt_Report", acViewReport
  83. End Sub
  84.  
Jul 21 '10 #37

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 21 '10 #38

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 21 '10 #39

AccessIdiot
100+
P: 493
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.
Jul 21 '10 #40

NeoPa
Expert Mod 15k+
P: 31,768
AccessIdiot:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Const conJetDate As String = "\#m\/d/yyyy\#"   'The format expected for dates in a JET query string.
  4.  
  5. Private Sub cmdApplyFilter_Click()
  6. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  7.     'Notes:     1. We tack " AND " on the start of each condition so you can easily add more search boxes; _
  8.                         we remove the leading " AND " at the end.
  9.     '           2. The date range works like this: _
  10.                         Both dates      = only dates between (both inclusive); _
  11.                         Start date only = all dates from this one onwards; _
  12.                         End date only   = all dates up to (and including this one); _
  13.                         Month or Year   = For Start date the first day of the period; _
  14.                                           For End date the last day of the period.
  15.  
  16.     Dim strFilter As String                 'The criteria string.
  17.     Dim strEnd As String, strDate As String
  18.     Dim lngLen As Long                      'Length of the criteria string to append to.
  19.     Dim intCount As Integer
  20.     Dim datStart As Date, datEnd As Date
  21.  
  22. ' Check that the report is open
  23.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Report") <> acObjStateOpen Then
  24.         MsgBox "You must open the report first."
  25.         Exit Sub
  26.     End If
  27.  
  28. 'If a combo box is empty its value is Null so I can use an If Statement to check
  29. 'whether or not the user made a choice and then construct the appropriate SQL:
  30.     With Me
  31.         If .cboType > "" Then
  32.             strFilter = " AND ([Nest Location]=" & .cboType & ")"
  33.         End If
  34.         If .cboIsland > "" Then
  35.             strFilter = strFilter & " AND ([Island Name]=" & .cboIsland & ")"
  36.         End If
  37.         'Handle Start Date
  38.         If IsNull(.txtStartDate) Then
  39.             datStart = #1/1/1900#
  40.         Else
  41.             datStart = CDate(IIf(InStr(1, .txtStartDate, "/") = 0, "1/1/", "") & .txtStartDate)
  42.         End If
  43.         'Now datStart set, proceed to datEnd
  44.         strEnd = Nz(.txtEndDate, "12/31/9999")
  45.         intCount = UBound(Split(strEnd, "/"))
  46.         If intCount = 0 Then
  47.             strEnd = "12/" & strEnd
  48.             intCount = 1
  49.         End If
  50.         datEnd = CDate(strEnd)
  51.         If intCount = 1 Then
  52.             datEnd = DateAdd("m", 1, datEnd) - 1
  53.         End If
  54.         'Now Dates are determined, formulate into Filter string
  55.         strDate = " AND ([Field Obs Date] Between %S And %E)"
  56.         strDate = Replace(strDate, "%S", Format(datStart, conJetDate))
  57.         strDate = Replace(strDate, "%E", Format(datEnd, conJetDate))
  58.         'Lose the first " AND " and add the Date filter
  59.         strFilter = Mid(strFilter & strDate, 6)
  60.     End With
  61.  
  62. 'Combine the criteria to form a WHERE clause for the filter:
  63.     Debug.Print strFilter
  64.     With Reports![rpt_Report]
  65.         .Filter = strFilter
  66.         .FilterOn = (strFilter > "")
  67.     End With
  68. End Sub
  69.  
  70.  
  71.  
  72. Private Sub cmdRemoveFilter_Click()
  73.      On Error Resume Next
  74.      Reports![rpt_Report].FilterOn = False
  75.      Me.cboType.Value = Null
  76.      Me.cboIsland.Value = Null
  77.      Me.txtEndDate = Null
  78.      Me.txtStartDate = Null
  79. End Sub
  80.  
  81. Private Sub Form_Load()
  82.     DoCmd.OpenReport "rpt_Report", acViewReport
  83. 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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1.  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.
Jul 21 '10 #41

AccessIdiot
100+
P: 493
Excellent explanation. I have just a few questions.

1) Line 53
Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 21 '10 #42

NeoPa
Expert Mod 15k+
P: 31,768
AccessIdiot: Excellent explanation.
Thank you :)
AccessIdiot: 1) Line 53
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. strDate = MultiReplace(" AND ([Field Obs Date] Between %S And %E)", _
  2.                        "%S", Format(datStart, conJetDate), _
  3.                        "%E", Format(datEnd, conJetDate))
The alternative would be :
Expand|Select|Wrap|Line Numbers
  1. strDate = " AND ([Field Obs Date] Between " & _
  2.           Format(datStart, conJetDate) & " And " & _
  3.           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.
Jul 22 '10 #43

AccessIdiot
100+
P: 493
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. :-)
Jul 22 '10 #44

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 22 '10 #45

AccessIdiot
100+
P: 493
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!
Jul 22 '10 #46

Post your reply

Sign in to post your reply or Sign up for a free account.