Building Advanced Search Criteria

Building Advanced Search Criteria

My second set of Videos on Building Search Criteria!

In this set of Videos I build on the information provided in the first set of videos here:-

Building Search Criteria

The code from the first set works ok, but it’s not that efficient. It could be Slow on really big data sets. Besides that, it’s just not right to leave your code in a less than perfect state! In this “Advanced Search” set, I also demonstrate some new techniques, specifically looking for patterns in the code, which often lead to smaller single functions instead of lots of individual functions.

I’ve recently upgraded these videos, adding more information and shortening the video length.

Total watch time now is about 28 minutes if you run the video at double speed.

A Bonus! I’m currently available to help you with any enquiries, just press the email button:- “Help with Building the Search Form”  BELOW and I will get back to you, usually within 24 hours…

… …

Where this Lesson is Heading

Where this Lesson is Heading

Video 7 (3:52)​

This Advanced but simple to use Search Form is easy to create following the comprehensive Video Instructions.

Prerequisites:-  I assume you have watched and completed the first set of videos here:- Building Search Criteria .

Video 7 (3:52)​

Video number 7, this is the last video in this set of Videos showing you how to build this excellent Search Form. I put the Last Video Here, at the beginning to whet your appetite! I want to show you what you can achieve by following the instructions in the videos below…

Total viewing time “Less than 56 minutes”

Need any help understanding anything, then drop me a line. Press the Green Button:-

“Help with Building the Search Form”

Don’t have 56 minutes to spare?

Then there’s a Quick and Easy Option. See my Ready to GO – Product here:-

A “Search Form” you can drop into your database and be with using within 4 minutes!

…         ..         ..            ..        ..          ..           ..     ..  ..         … 

… …

A Step Backwards - Remove "Like"

Advanced Search Criteria 1

Video 1 (3:41)

Sometimes you need to go a step back before you can go forward. In this video we’re going to remove the “Like”. This makes The Searches “Exact” without any fluffiness! This step also messes up the “Return All Records” aspect of the form.

Now you’ve seen where this set of training videos is taking you let’s go through a few preliminaries.

IF YOU want to Skip Ahead, then SEE THIS PAGE where there’s a full list of all the bullet points from the videos.

You can pick a place where you’d like to start.

These comprehensive Video Instructions demonstrate how to add this incredible Search utility to your database.

With it, you can establish yourself as the “Go To” person in your organisation for database improvements!

Nifty Access Drop in “Nifty Components” will quickly elevate you to “Power User Level!” 

Video 1 (3:41)​

Advanced Search Criteria 1 - Bullet Points:-

00:30  identified that the SQL statement contains superfluous characters
00:40 we can get rid of the “LIKE!!
01:35 incorrectly formatted SQL Statement Returns what it thinks is a variable instead of the text
01:55  be incorrectly formatted SQL statement causes access to ask you for a Parameter in a pop-up window
2:10  a quote mark is required and is provided by entering character 34 Chr(34)
03:00 removing the word “Like” causes the SQL statement to no longer return all the records
03:18 removing the “Like” from the SQL statement causes the other criteria to fail

… …

Updating the SQL Statement

Advanced Search Criteria 2

Video 2 (4:58)

SQL Statement as Text – In this video I discuss the building of the SQL Statement…
Now don’t be overawed by this, all you are doing is putting various text strings together, which is a basic task in VBA. In this case you are handling the text version of an SQL Statement. It is the most basic “Text” manipulation, concatenating different bits of text together. However it’s not quite as simple as that, because you have to understand the logic in the SQL Statement. That’s the key to it, understanding what the SQL Statement is doing.

Understandably, you may have difficulty in understanding the SQL Statement in its Text form because like practically everyone using MS Access, your experience with SQL Statements will be through the Query Builder Grid, you won’t have had that much experience with the actual text version of the SQL Statement. If that’s the case then I suggest you put the SQL in the Query Builder Grid and mess around with it in there. I’m assuming that like most people you have had quite a bit of experience in using the Query Builder Grid and now want to advance your skills and start manipulating the SQL in VBA code

Video 2 (4:58)

Advanced Search Criteria 2 - Bullet Points:-

00:08  with the “Like” clause removed no records are Returned
00:17 if you input criteria like “Smith” then only records that exactly match “Smith” are Returned
00:30 the problem is we are searching for an empty string, there are no empty strings so we don’t get any records Returned
01:54 let’s not ask that question!
02:14 we can do an experiment by feeding in an empty string and see what results we get
03:08 using an IF Statement to ask a question in your VBA code
04:05 also test for a NULL value – with “IsNull”

… …

Improving the SQL Statement?

Advanced Search Criteria 3

Video 3 (5:15)

The attempts to Improve the SQL statement are causing more problems, not unexpected, but again it will need a bit of thought and experimentation to sort out.

Video 3 (5:15)

Advanced Search Criteria 3 - Bullet Points:-

00:43  no records are Returned because we are asking for an exact match
01:33 the changes to the way the SQL statement is being built also affect the where clause
03:20  using “OR” in the If Statement does not return the correct Where Clause
03:47 change the “OR” to “AND” and see if that Returns the correct “WHERE CLAUSE”
04:30  the updated code is no longer returning the correct where clause when the Search Criteria is omitted

… …

An alternative way to Return "All"

Advanced Search Criteria 4

Video 4 (4:18)

Because we’ve removed “Like” the SQL won’t return all the records. Therefore we need an alternative way of returning all the records. An alternative way to think about the problem is to realise that if all the textboxes are empty, then we need to see all the records! We already have a function which checks to see if the textboxes are empty. We can tap into that function by using an If Statement to either provide the SQL containing the Search Criteria or alternatively provide an SQL Statement which lists all of the Records.

Video 4 (4:18)

Advanced Search Criteria 4 - Bullet Points:-

00:20 appears to work OK if Search Criteria is provided
00:26 if you omit the Search Criteria, then the SQL Statement isn’t formed correctly
00:29 it appears to be incorrectly removing extra characters from the SQL Statement
00:38 reduce the number of characters removed from 5 to 0
02:05 it looks like the “AND” is not being removed from the end of the SQL Statement
02:30 the fWhere Function provides exactly what we want, it tells us that the text boxes are empty
03:00 I added an If Statement that checks what is returned by the function “fWhere” 
03:23 the code now correctly filters based on the textbox contents
03:33 and it also returns all the records when ALL the textboxes are empty
03:45 next we need to look at reintroducing wild cards into the search criteria

… …

Find Patterns in your Code

Advanced Search Criteria 5

Video 5 (5:43)

Demo XXX

Video 5 (5:43)

Advanced Search Criteria 5 - Bullet Points:-

00:07 adding a star directly into the textbox did not allow it to return prefix or suffix characters
00:39 code tidying up session
01:00 three of the functions have a similar layout
01:18 it is apparent that the functions have a similar structure/pattern
01:45  we can transform the three functions into one function by adding parameters to the function
02:00 it’s a good idea to pass arguments into a function where possible
02:23 use find and replace to change “ProgrammeTitle” to “strFld”
02:35 using Replace confined to a procedure
02:51 now replace all the occurrences of the text box with a textbox object variable
03:13 now you need to amend all of the occurrences of the functions to include the newly created arguments
03:15 “argument not optional” error
04:00 variable incorrectly entered as a String
04:24 concatenate the variable into the SQL string with ampersands
04:45 one function has been created to replace three functions

… …

Create an Option Group

Advanced Search Criteria 6a

Video 6a (3:10)

In this short video I cover quite a bit of ground. I explain the importance of documenting your code, and where possible, make the code document itself “Self Documenting” — The location of the code, the actual working code lies between the start and finish of the error handler (Sort of) more about this later I reckon! I demonstrate to creating an Option Group (but it’s not wired up to anything) see that in the next video!

Video 6a (3:10)

Advanced Search Criteria 6a - Bullet Points:-

00:44 example of how your code can automatically identify itself
01:16 the code we interested in is between the start and finish lines of the error handler
02:00 adding an “Option Group” to set the Criteria
02:25 cannot have two options with the same name
02:40 the option group selections return an integer

… …

Wiring up the Option Group

Advanced Search Criteria 6b

Video 6b (6:54)

In this video I cover Wiring up the Option Group to return it’s value to the function by adding the a reference to the Option Group as argument in the function. I demonstrate an easy way to create SQL Statements

Video 6b (6:54)

Advanced Search Criteria 6b - Bullet Points:-

00:33 the option group is not working yet
00:40 how do we wire it up?
00:58 what we need is the number Returned by the Option Group
03:35 one of the best kept secrets on Microsoft Access is how easy it is to handle SQL Statements
04:05 I don’t write SQL statements!!!
04:10 the easy way to create SQL Statements in MS Access
05:15 if the SQL Statement generated by the query grid is based on a single table, you can remove the table name
05:20 using find and replace

… …

Wiring up the Case Statement

Advanced Search Criteria 6c

Video 6c (8:53)

In this video I create a rudimentary Case Statement. I show the Case Statement selecting the SQL to provide an exact match. Then I show how to create an SQL Statement with matching wildcards each side of the text passed in from the textbox. I show that the SQL is incorrectly formatted because it has too many Quotation Marks in it. I show how to discover this using a breakpoint in the code. I demonstrate how easy it is to see where the mistake is by using my particular style of building SQL Statements

Video 6c (8:53)

Advanced Search Criteria 6c - Bullet Points:-

00:34 case statement added to the code
01:18 forgot to comment out the line of code!
01:38 use the query builder grid to create your SQL Statement
01:59 extract the text version of the SQL Statement from the query builder grid
04:55 insert a breakpoint so that you can see the code in operation
05:18 one of the advantages of building your SQL Statements out of string variables is that it’s easier to see where things have gone wrong when you are debugging
05:40 see how MS Access identifies an empty string when you are debugging
06:50  whilst debugging the SQL statement we find superfluous quotation marks
07:38 remove all the breakpoints easily with debug “remove breakpoints”

… …

SQL - Case & NULL

Advanced Search Criteria 6d

Video 6d (6:44)

Completing the SQL Statements for the select Case Statement. Demonstrating using a breakpoint to detect the location of an error in the SQL Statement. I also discuss and demonstrate the NULL Value which is the absence of data! ( I’m not talking about  “Star Trek Next Generation” Data)… Demonstrate how to add an SQL Statement for detecting NULL into the Select Case Statement

Video 6d (6:44)

Advanced Search Criteria 6d - Bullet Points:-

01:00 adding a breakpoint to the code again for debugging
01:30 as you build the code you can often remove superfluous string variables
02:10 what is NULL? (IsNull) I provide a good explanation of what “NULL” is, in relation to data in an MS Access table, even though I say so myself!
02:30 using “NULL” as criteria in a query built with the query builder grid in MS Access
02:48 my excellent description of “NULL” (IsNull) here
03:30 the difference between “NULL” and an empty string
05:20  looks like the code is removing the last five characters

… …

General Tidy Up!

Advanced Search Criteria 6e

Video 6e (1:58)

The completed Case Statement. General tidy-up of the code and demonstration of it working. I mention the importance of having just one piece of code called many times. When you make a change to code, you only need to change the code in one place, not many!

Video 6e (1:58)

Advanced Search Criteria 6e - Bullet Points:-

01:05 a look at the nicely laid out code
01:30 demonstrating that the code automatically works with each group of controls
01:38 by utilising a function, then the number of controls the code works with is practically unlimited (save for the maximum number of controls on a form of around 740)

… …

General summing Up!

Advanced Search Criteria 7

Video 7 (3:52)

When I remastered my videos I think I messed this one up! I’ll leave it in for now, but I might need to remove it, or replace it with a different video in the near future. It’s sort of a recap of a recap and I’m a bit hesitant in it sounds like I’m tired so…

Video 7 (3:52)

Advanced Search Criteria 7 - Bullet Points:-

00:44 it’s a good idea to identify object variables. My preference is to precede the name with “o” denoting object
01:04  the left and right stars where the same. I changed them to a single SQL string representing the Star
01:55  the value from the option group is passed as a parameter through the function fSearchFld
02:22 the option group value is used in the Case Statement to select the correct SQL Statement
02:44 I have cleaned up the code, got rid of old redundant code. Did a bit of formatting
03:00 the “WHERE clause” code is hard coded to test each individual text box. It would make a lot of sense to update this test. Develop a function to do this. DONE! This is done in the next set of videos see here:-

… …