Solved Between Dates On Keyword Search

[Emma35]

Hi All,
I've used the code in this thread for searching my databases quite a bit and find it excellent.

https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/

I was wondering if anyone is familiar with it, could you tell me is it possible to add a 'Between Dates' search. I can do a single date search but can't get the other one to work. The thread is closed so can't ask the original author.

Thanks a lot, Emma

 

[MajP]

It is up to you if you like that method, but in my opinion that example is complete disaster. That would take me forever to attempt to get the syntax correct, and impossible to debug.

I could build that same filter in 5-10 minutes and be more flexible. This is my approach. I include an example with multi select controls, between date controls, standard combos, text boxes, and other controls.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Emma35]

Thanks MajP....i had a look at the thread and the example. Very impressive work although a little complicated for a novice like me. I'd certainly be willing to attempt it in a future database but not sure i could swap it for my current method in this project. I'm a little confused as to why you think it's so bad, it's always worked very well for me ?

 

[MajP]

I'm a little confused as to why you think it's so bad, it's always worked very well for me ?

It probably works fine in many cases, but your question is a prime example of the limitation. IMO that approach
Extremely tedious
Impossible to debug
Cannot handle multi select listboxes, between filters, other controls
No error checking

But that is my opinion, if it works for you then stick with it.

although a little complicated for a novice like me.

The code behind the scenes may be complicated, but IMO using it is very easy if you follow the pattern.

 

[MajP]

If you want to post your search form, I can better help. You can scramble data if necessary

Scramble Text

This function replaces every character with a random similar character. I use this function to anonymize data in tables. Almost but not quite entirely unlike the post made by Micron a few months ago. Share & Enjoy! Option Compare Binary Option Explicit Public Function ScrambleIt(strString...

www.access-programmers.co.uk

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

 

[Emma35]

Thanks again MajP. I've attached a stripped down version of the database.....had to delete quite a lot of data for the usual reasons sorry.
I think there's still enough there for you to see what's going on. The form with the search function is called frm_SearchMulti
I need the Date Reported control to become two controls where you can enter two dates and get all the results between those dates

Thank you
Em

 

[MajP]

So why I prefer my method?

Here is my entire code to include the between date filter, and a means to toggle from an And to Or filter

Public Function FilterList()
  Dim fltrNonConID As String
  Dim fltrNonConType As String
  Dim fltrDates As String
  Dim fltrPlant As String
  Dim fltrLocation As String
  Dim fltrReported As String
  Dim fltr As String
  Dim AndOr As CombineFilterType
  Dim strSql As String

  Select Case frameAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select

  fltrNonConID = GetFilterFromControl(Me.cmboID)
  fltrNonConType = GetFilterFromControl(Me.cmboType)
  fltrPlant = GetFilterFromControl(Me.cmboPlant)
  fltrLocation = GetFilterFromControl(Me.cmboLocation)
  fltrReported = GetFilterFromControl(Me.cmboReported)
  fltrDates = GetBetweenFilter(Me.txtBeginDate, Me.txtEndDate, "DateReported")
  fltr = CombineFilters(AndOr, fltrNonConID, fltrNonConType, fltrPlant, fltrLocation, fltrReported, fltrDates)
  Me.txtFilter = fltr


  strSql = "Select * from qry_SearchAll_NoCriteria"
  If fltr <> "" Then
    strSql = strSql & " WHERE " & fltr
  End If
  strSql = strSql & " Order By DateReported DESC"
  Me.SearchResults.RowSource = strSql
End Function

This is about 150 lines less code then yours, even with the added functionality.

This is my query

SELECT tbl_maintracker.id,
       tbl_maintracker.datereported,
       tbl_maintracker.reportedby,
       tbl_maintracker.incidenttype,
       tbl_maintracker.plant,
       tbl_maintracker.location,
       tbl_maintracker.monitorid,
       tbl_maintracker.duedate,
       tbl_maintracker.status
FROM   tbl_maintracker
ORDER  BY tbl_maintracker.datereported DESC;

This is your query

SELECT tbl_maintracker.id,
       tbl_maintracker.datereported,
       tbl_maintracker.reportedby,
       tbl_maintracker.incidenttype,
       tbl_maintracker.plant,
       tbl_maintracker.location,
       tbl_maintracker.monitorid,
       tbl_maintracker.duedate,
       tbl_maintracker.status
FROM   tbl_maintracker
WHERE  ( ( ( tbl_maintracker.id ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                       [srchtext] &
                                                "*" )
         AND ( ( tbl_maintracker.datereported ) LIKE
                     "*" & [forms] ! [frm_searchmulti] ! [srchtext1] & "*"
                OR ( tbl_maintracker.datereported ) IS NULL )
         AND ( ( tbl_maintracker.reportedby ) LIKE
               "*" & [forms] ! [frm_searchmulti] !
                     [srchtext2] & "*"
                OR ( tbl_maintracker.reportedby ) IS NULL )
         AND ( ( tbl_maintracker.incidenttype ) LIKE
                     "*" & [forms] ! [frm_searchmulti] ! [srchtext3] & "*"
                OR ( tbl_maintracker.incidenttype ) IS NULL )
         AND ( ( tbl_maintracker.plant ) LIKE "*" & [forms] ! [frm_searchmulti]
                                              !
                                              [srchtext4]
                                              &
                                                    "*"
                OR ( tbl_maintracker.plant ) IS NULL )
         AND ( ( tbl_maintracker.location ) LIKE
               "*" & [forms] ! [frm_searchmulti] !
                     [srchtext5] & "*"
                OR ( tbl_maintracker.location ) IS NULL )
         AND ( ( tbl_maintracker.monitorid ) LIKE
               "*" & [forms] ! [frm_searchmulti] !
                     [srchtext6] & "*"
                OR ( tbl_maintracker.monitorid ) IS NULL ) )
ORDER  BY tbl_maintracker.datereported DESC;

Debugging those parameterized queries IMO is painful. I can debug in pieces. I added a filter display that you can remove if you verify it works. Please verify it works. I found a significant but in my code, but think I fixed it.

 

[Emma35]

MajP......thanks for the updated file. When i enter a date in the first field i get the error message below ?

 

[MajP]

I am unable to replicate that issue. It works fine for me. Maybe delete the version you have and download again.

 

[Emma35]

Ok i deleted and re-downloaded the file but error still happening. It's actually occurring when i use the other criteria also and not just the dates.....very odd

 

[MajP]

Weird. What version of Access and what language? Go into visual basic and hit "Debug", "Compile". See if this compiles

In all of those controls in the afterupdate event I have this property
=FilterList()

So those controls all call the function

Public Function FilterList()
  Dim fltrNonConID As String
  Dim fltrNonConType As String
  Dim fltrDates As String
  Dim fltrPlant As String
  Dim fltrLocation As String
  Dim fltrReported As String
  Dim fltr As String
  Dim AndOr As CombineFilterType
  Dim strSql As String
  Me.Dirty = False
  Select Case frameAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select
 
  fltrNonConID = GetFilterFromControl(Me.cmboID)
  fltrNonConType = GetFilterFromControl(Me.cmboType)
  fltrPlant = GetFilterFromControl(Me.cmboPlant)
  fltrLocation = GetFilterFromControl(Me.cmboLocation)
  fltrReported = GetFilterFromControl(Me.cmboReported)
  fltrDates = GetBetweenFilter(Me.txtBeginDate, Me.txtEndDate, "DateReported")
  fltr = CombineFilters(AndOr, fltrNonConID, fltrNonConType, fltrPlant, fltrLocation, fltrReported, fltrDates)
  Me.txtFilter = fltr
 
 
  strSql = "Select * from qry_SearchAll_NoCriteria"
  If fltr <> "" Then
    strSql = strSql & " WHERE " & fltr
  End If
  strSql = strSql & " Order By DateReported DESC"
  Me.SearchResults.RowSource = strSql
  Me.SearchResults.Requery
End Function

My first guess is for some reason, it thinks it cannot find this function. Or I have a reference that you do not, so it is not compiling.

 

[MajP]

FYI. Whenever you get that error which is common, do a compile and you will find it is actually some other part of code that is not compiling correctly. Often it is not the place where you expect. However, this compiles fine on my end, so maybe some kind of version issue.

 

[MajP]

I see the file has 9 views on it, so hopefully someone else will chime in if they got it to work.

 

[Emma35]

I checked that reference and it was ticked on my list also. The debug and compile showed nothing. So, i sent the file to a colleague and it worked on her computer. I decided to do the tried and trusted 'Restart the computer' solution and it works perfectly now. Just as well, i was about to fetch my diagnostics hammer and perform some percussive maintenance !. Thank you very much MajP for your advice and patience.....i appreciate it a lot.

Em x

 

[MajP]

Again, this is just my opinion but I find those parameterized queries extremely hard to work with on more complex tasks like this. It is extremely hard to debug and not flexible.

Also I added some combos where you had textboxes. Example, reported by. The user can still type if they want vice select from list, but this will enforce that they only can choose actual values. Also all combos should set Limit to List: Yes, Inherit Value List: No

Also I like to make my combos only pull data that exists in the data table. So you may have tblPlants with 20 plants, but only 3 exist in your data table.
instead of

Select PlantID, PlantName from tblPlants

I use

SELECT DISTINCT tbl_Plants.PlantName AS Plant
FROM tbl_Plants INNER JOIN tbl_MainTracker ON tbl_Plants.PlantName = tbl_MainTracker.Plant
ORDER BY tbl_Plants.[PlantName];

This way not allowing the user to choose something that does not exist.

 

[Emma35]

Again, this is just my opinion but I find those parameterized queries extremely hard to work with on more complex tasks like this. It is extremely hard to debug and not flexible.

Also I added some combos where you had textboxes. Example, reported by. The user can still type if they want vice select from list, but this will enforce that they only can choose actual values. Also all combos should set Limit to List: Yes, Inherit Value List: No

Also I like to make my combos only pull data that exists in the data table. So you may have tblPlants with 20 plants, but only 3 exist in your data table.
instead of

Select PlantID, PlantName from tblPlants

I use

SELECT DISTINCT tbl_Plants.PlantName AS Plant
FROM tbl_Plants INNER JOIN tbl_MainTracker ON tbl_Plants.PlantName = tbl_MainTracker.Plant
ORDER BY tbl_Plants.[PlantName];

This way not allowing the user to choose something that does not exist.

Yes i see you changed it to a combo box....probably a better idea than leaving it for people to just type in.
I've tried to use your code in the existing database but i'm getting that error again. I've imported the two modules, used your 'No criteria' query and add the =filterList() command to the AfterUpdate event. Have i missed something or for gotten to edit something else ?.
For some reason, the Restart isn't working this time ?

 

[MajP]

Did you try a debug, compile to see if it finds an error?

 

[Emma35]

Ok so the compiler stopped at the error message attached

 

[MajP]

That says you are missing a reference to the DAO library. That reference should come as a default, so I am surprised it is missing. Is this a very old version of Access? You should add a reference to something like Microsoft Office 16 Access database engine object.

 

[Emma35]

This is my version of Access

Microsoft® Access® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20764) 64-bit

The reference you mentioned wasn't actually ticked so i fixed that. I ran the compiler again and got no more errors. I also did a compact and repair for good measure but still getting the error. Thing is, the sample file you gave me doesn't give the error and it's running on the same machine ?