UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Range Parameters Not Being Passed To Underlying Query, Access 2016    
 
   
Oblio
post Feb 14 2018, 01:27 PM
Post#1



Posts: 204
Joined: 5-February 15



Hi,

I have a bound form and on that form in the header section are two textboxes: txt_Search_Start_Date and txt_Search_End_Date, both in short date format.

A command Button named cmd_Search_By_Date is on the form header and here is it's on click event VBA code I found on the internet and modified to my needs, which may be causing the parameters not to be passed to the underlying form query to filter the records to the appropriate date range.

CODE
Private Sub cmdSearchByMonth_Click()
'Search button on form "Search By Month Only"
Call Search

End Sub

Sub Search()
Dim strCriteria, Task As String

Me.Refresh

If IsNull(Me.txt_Search_Start_Date) Or IsNull(Me.txt_Search_End_Date) Then
    MsgBox "Please fill in the entire date range", vbInformation, "Full Date Range Required"
    Me.txt_Search_Start_Date.SetFocus
Else

    strCriteria = "([MCR_Course_Start_Date] >= #" & Me.txt_Search_Start_Date & "# And [MCR_Course_Start_Date] <= #" & Me.txt_Search_End_Date & "#)"
    Task = "select * from q_Search_By Month_Only_EXTENDED where (" & strCriteria & ") order by [MCR_Course_Start_Date]"
    DoCmd.ApplyFilter Task
  
End If
    
End Sub


It compiles correctly, but does not supply the selected dates even though I can see it is the correct filter in the immediate window...

The date I wish to apply the date range to is named "MCR_Course_Start_Date" and is a text field in the detail section of the form which is bound to the query "q_Search_By Month_Only_EXTENDED"

I have been at this three days steady with no luck so far... Can anyone offer some advice?

pullhair.gif
Go to the top of the page
 
ranman256
post Feb 14 2018, 01:30 PM
Post#2



Posts: 860
Joined: 25-April 14



make a query
see if it works as a query, THEN make an SQL.
This post has been edited by ranman256: Feb 14 2018, 01:31 PM
Go to the top of the page
 
Oblio
post Feb 14 2018, 01:34 PM
Post#3



Posts: 204
Joined: 5-February 15



So, I would look at the SQL of the query WITH the form parameters listed in the criteria field of each startdate and endate?
Go to the top of the page
 
LPurvis
post Feb 14 2018, 01:37 PM
Post#4


UtterAccess Editor
Posts: 16,270
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I'm not sure of the date format in your region... but it's best to remove ambiguity.

strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"

If a query grid is used, it'll convert to your locale automatically.

Cheers

--------------------
Go to the top of the page
 
Oblio
post Feb 14 2018, 01:48 PM
Post#5



Posts: 204
Joined: 5-February 15



Tried your suggestion and Rayman's as well. I do get mostly the date range I have supplied, either by parameter form OR by the parameter query input prompts, however I get results outside of the ending "date to"...

So if the date from is entered as "2017/09/01", NO DATES are returned before this, ONLY dates after the date entered...so far so good. HOWEVER, on the other side we get dates past the end date entered as a parameter...no idea why....I have checked the spelling of the end field and it is correct...
Go to the top of the page
 
BruceM
post Feb 14 2018, 01:54 PM
Post#6


UtterAccess VIP
Posts: 7,367
Joined: 24-May 10
From: Downeast Maine


I am not very familiar with ApplyFilter, but I believe it is essentially similar to setting a string as a filter, then applying the filter. However, your Task string looks like a record source, not a filter. Instead of Docmd.ApplyFilter you could do:
Me.Recordsource = Task

If you intend to apply a filter, just use strCriteria as the filter, taking care with the date format as suggested.

If applying a filter I would do:

Me.Filter = Task
Me.Filteron = True
Go to the top of the page
 
projecttoday
post Feb 14 2018, 02:12 PM
Post#7


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


To avoid the start date / end date ambiguity, use the DateDiff function instead of >= and <=/

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Oblio
post Feb 14 2018, 02:41 PM
Post#8



Posts: 204
Joined: 5-February 15



Hi Bruce, thanks for your help !

So my code would look like this ?

CODE
Private Sub cmdSearchByMonth_Click()
'Search button on form "Search By Month Only"
Call Search

End Sub
Sub Search()
Dim strCriteria, Task As String

Me.Refresh

If IsNull(Me.txt_Search_Start_Date) Or IsNull(Me.txt_Search_End_Date) Then
    MsgBox "Please fill in the entire date range", vbInformation, "Full Date Range Required"
    Me.txt_Search_Start_Date.SetFocus
Else

    'strCriteria = "([MCR_Course_Start_Date] >= #" & Me.txt_Search_Start_Date & "# And [MCR_Course_Start_Date] <= #" & Me.txt_Search_End_Date & "#)"
    strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"
    Task = "select * from q_Search_By Month_Only_EXTENDED where (" & strCriteria & ") order by [MCR_Course_Start_Date]"
    'DoCmd.ApplyFilter Task
    Me.RecordSource = Task
    Me.Filter = Task
    Me.FilterOn = True
End If
    
End Sub
Go to the top of the page
 
BruceM
post Feb 14 2018, 02:59 PM
Post#9


UtterAccess VIP
Posts: 7,367
Joined: 24-May 10
From: Downeast Maine


One or the other. Either:

strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"
Task = "select * from q_Search_By Month_Only_EXTENDED where (" & strCriteria & ") order by [MCR_Course_Start_Date]"
Me.RecordSource = Task

or:

strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"
Me.Filter = strCriteria
Me.FilterOn = True

The first one applies strCriteria to the recordset, so there is no need to apply it again as a filter. In any case, the filter would be strCriteria, not Task. A filter is the Where condition for a query, without the word "WHERE".

The second one assumes the form is based on q_Search_By Month_Only_EXTENDED, since that is the recordset you wish to filter. If the form is based on another recordset you will need to use the RecordSource approach.

In any case, you should be able to use Between...And for the criteria:

"[MCR_Course_Start_Date] Between #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & " And " & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#"

Caveat: Robert and others who responded in this thread post good responses to a variety of questions in this forum, and I have gone in a different direction than indicated in their replies, so I find myself wondering if I missed something.
Go to the top of the page
 
Oblio
post Feb 15 2018, 07:29 AM
Post#10



Posts: 204
Joined: 5-February 15



Hi Bruce,

The second approach worked:

CODE
strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"
Me.Filter = strCriteria
Me.FilterOn = True


The form IS based on the query as you had anticipated.

The first approach failed and indicated the Select clause query did not exist. It is the same query underlying the form and I copies and pasted the name of the query so am wondering do I need to do a querydef declared...afraid I have just run across that term recently but have not had time to look into it yet ?

This failed in the query grid and said there was an invalid date format:

CODE
"[MCR_Course_Start_Date] Between #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & " And " & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#"


This portion was highlighted:
CODE
#" & Format([Me].[txt_Search_Start_Date],"yyyy-mm-dd") & " And " & Format([Me].[txt_Search_End_Date],"yyyy-mm-dd") & "#


Again many, many, thanks for your help and explanations...they are very valuable !!!

Bill
Go to the top of the page
 
Oblio
post Feb 15 2018, 07:37 AM
Post#11



Posts: 204
Joined: 5-February 15



Hi,

I am curious as to what that would look like... I am trying to return all records between the dates selected, inclusive.

Would you be able to show me how I could use DateDif to accomplish this? I have only ever used it to return the number of days, say 9, which I am not sure how that would translate into returning all the records...

Thanks for your help !

Bill

Go to the top of the page
 
Oblio
post Feb 15 2018, 07:41 AM
Post#12



Posts: 204
Joined: 5-February 15



Thank you for your help !!!

I ended up going with the only solution that worked for me from Bruce:

CODE
strCriteria = "([MCR_Course_Start_Date] >= #" & Format(Me.txt_Search_Start_Date, "yyyy-mm-dd") & "# And [MCR_Course_Start_Date] <= #" & Format(Me.txt_Search_End_Date, "yyyy-mm-dd") & "#)"
Me.Filter = strCriteria
Me.FilterOn = True


Thank you for your help...

Bill
Go to the top of the page
 
Oblio
post Feb 15 2018, 07:43 AM
Post#13



Posts: 204
Joined: 5-February 15



Thank you for your help.... Afraid I was unable to get a query working...I used Bruce's suggestion further on in the conversation.
But thanks again for your offer to help !

Bill
Go to the top of the page
 
BruceM
post Feb 15 2018, 11:01 AM
Post#14


UtterAccess VIP
Posts: 7,367
Joined: 24-May 10
From: Downeast Maine


For the criteria you may find it more convenient to use the Between...And construction I mentioned near the end of my previous posting, but if I understand correctly you have it working, so best of luck with the project.
Go to the top of the page
 
LPurvis
post Feb 15 2018, 11:23 AM
Post#15


UtterAccess Editor
Posts: 16,270
Joined: 27-June 06
From: England (North East / South Yorks)


Wow this has moved on. Glad you got there. I'd not noticed the use of ApplyFilter (can't find a vomiting emoji when you need one.)

I'd say you're fine with the >= and <=, optimisation-wise, there's nothing between it and BETWEEN really.
Though I'd generally go for >= and <, and supply a date one day greater than that which you want, as time valeus are handled very well then.

I'd have to say that I'd certainly want to avoid DateDiff, which would be for the previously mentioned optimisation reasons. (It's considerably less effiecient.)

Cheers!

--------------------
Go to the top of the page
 
Oblio
post Feb 15 2018, 11:53 AM
Post#16



Posts: 204
Joined: 5-February 15



Thank you for the info ! I forgot that I had seen the +1 day while searching through the internet...thanks for reminding me about that !

Afraid I have been struggling all year to get a Search form working as it contains a date range (2 textboxes) and 2 independent comboboxes, one Course type and the other an Instructor's name...

Well thanks very much for your help, and I hope you have a great day !!!

Bill
Go to the top of the page
 
projecttoday
post Feb 15 2018, 09:17 PM
Post#17


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


You don't need +1 with DateDiff. Even if it is less efficient than Between, the difference may not be noticeable.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
LPurvis
post Feb 16 2018, 06:49 AM
Post#18


UtterAccess Editor
Posts: 16,270
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Not to drag this on or anything but...

You don't need +1? It's not really as if that's some sort of hindrance to using >= and <. It's a genuine method chosen for a reason.
You can use >= and <= without the +1, but it's a nice alternative to have. (Adding 1 to the criteria date does nothing as far as harming the efficiency of the query.)

As for the difference perhaps not being noticeable, that's almost certainly true for small data sets.
But as a practice, maintaining SARGability in querying is a good policy to adopt and a lesson I always feel is important to impart.
When the data set grows, running a function on the field (especially an indexed one) will be a performance drain. And with large sets, definitely noticeable.

That's not to say that nobody should ever use DateDiff for this purpose. If nothing else, it's a useful learning position. But when there's a more efficient alternative I'd move as soon as I understood the difference.
The choice is each person's of course, as always.

Cheers

--------------------
Go to the top of the page
 
projecttoday
post Feb 16 2018, 08:40 AM
Post#19


UtterAccess VIP
Posts: 9,515
Joined: 10-February 04
From: South Charleston, WV


As oblio has discovered, you need +1 if (1) you're using >= <= or Between, and (2) there are time entries along with the date entries in the data. This is to get the full range of data specified. As I recall, if there are no time entries at all then you don't need it. I don't have any evidence regarding the performance of >= <= / Between versus DateDiff.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st May 2018 - 12:19 AM