access 2010 search form.

creating the 4 unbounded fields is fine, its how to get them to work as search items with the query is the problem.  I need to know how to get the form to work with the query as they do in the video, before and after LIKE criteria is applied.
Al Jr GriffenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In a Search form, you would typically build up a standard SQL statement based on the selections/inputs from the user. So if I have 2 boxes (txName and txType), then I'd do something like this:

Dim sql As String
sql = "SELECT * FROM SomeTable"

Dim where as String

If Nz(txName, "") <> "" Then
  where = " Customer_Name='" & Me.txName & "'"
End If

If Nz(txType, "") <> "" Then
  If Len(where)>0 Then
     where = where  & " AND "
  End If
  where = where & " txType='" & Me.txType & "'"
End If

If Len(where) > 0 Then
  sql = sql & " WHERE " & where
End If

From there, you'd use the "sql" variable to fill a listbox, or as the Recordsource for a subform, etc.

If you want to use LIKE, then you need to use wildcards:

If Nz(txName, "") <> "" Then
  where = " Customer_Name LIKE '*" & Me.txName & "*'"
End If

The above would show any match in Customer_Name for the value entered in txName. So if I enter "man" in txName, it would match on Manfred, Tremane, and Gruman, for example.

You can also use "preceding" and "trailing" matches:

If Nz(txName, "") <> "" Then
  where = " Customer_Name LIKE '*" & Me.txName & "'"
End If

This would match on Truman but not Manfred.

If Nz(txName, "") <> "" Then
  where = " Customer_Name LIKE '" & Me.txName & "*'"
End If

This would match on Manfred, but not Truman.

Note too that if your values are Numeric, you do not need the single quotes:

If Nz(Me.txType, "") = "" Then
  where = " Customer_Type=" & Me.txType
End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helen FeddemaCommented:
See my Fancy Filters form sample database for an example of a form with unbound combo boxes for filtering by items of various data types.  Here is a link:

and here is a screen shot of the form:

Fancy Filters form
Al Jr GriffenAuthor Commented:
Can this be done in Access without the use of VB code or any other coding, or does the coding provide the link between the unbounded fields/combo box and the user's choice of values.  Where do you put in the body of the code in Access when the features of the application fall short of finding a way to solve the problem manually ?

It seems some people solve every issue via VB or SQL code.  Others just pump the limits of Access.

I am looking for an approach toward defining when to code , where to code and when (for which classes of problems) not to use code.  Care to give any feedback on this issue or more specifically the youtube video I sent you all first. He was able to list 4 unbounded fields on a form and filter using a query with no apparent connection between them ( no coding connection specified)   Any illuminating comments ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The video you referenced using direct Form references in the query. For example, in the criteria row of the query, the Fields that are being searched would show something like this:


This works okay, but the methods shown by Helen's example are much more commonly used, and provide you with a lot more control over the process.

It seems some people solve every issue via VB or SQL code.  Others just pump the limits of Access.
Most developers quickly learn that drag-n-drop "programming", as described in the video, tends to leave you short of what you want. Access can do a lot of amazing things right out of the box, but in very short order you'll find that you need to learn VBA and SQL in order to take it beyond the bare basics.

I am looking for an approach toward defining when to code , where to code and when (for which classes of problems) not to use code.
I don't think anyone could really give you proper advice on that, since much of the decision comes down to specific application requirements, and your own skill set and comfort level. As you've seen with this question, you could do this entirely with a Query, or you could do it entirely in VBA/SQL. Either will work, so it's sort of a toss up in this case. In other cases, you might find a VBA method is much better than trying to muddle through with a bound form method.

FWIW, I don't advocate binding a query to a single form in the manner described in the video. You tend to end up with a LOT of nearly identical queries that are bound to their own objects, and when it's time to make a change you'll have a lot of work on your hands to make sure the change migrates to all those queries. You're much better off learning how to use generic queries, and manipulate them to your own needs - at least in my opinion.

the method in the video shows a way to simply get data out of your database - but no serious developer would use that method. It's what can be called "quick and dirty".

The problem is that you CAN of course open a query directly and you'll see the data the query delivers - so far so good. But a query returns only data and the "form" you see is nothing else than a graphic output of the recordset so you can edit it. And that's exactly the problem: You CAN in most cases edit any field in any row like you want, you as the developer has absolutely no control over what is entered here. A query (or table) window should only be used by developers and is not intended to be presented to the end user. That's the reason why forms exists where you can catch any input and react on it using a set of events on form and controls level.
Yes, you can define some rules on table level to make it impossible to enter data, but these rules are very basic. For anything more complex you will find no way around programming. That means: VBA or (since A2013) also table macros, that means, you need also to learn those macro stuff which only Microsoft think that's "programming".

So the answer to "some people seem to use always SQL and VBA" is of course: If you work with a database you need to learn SQL (and that's really no heavy work) - creating queries with the query designer is a good first step to do that and thinking that working with the query designer would be "something else" than working with SQL is wrong: At the end the designer creates a SQL command and that's what's being executed, so this is only a first way to get around the command level - but no way to get around SQL. A database without queries and SQL doesn't make much sense.
VBA is the next level: Anyone who wants to get to a better usability than Access offers out-of-the-box simply will need VBA (or, like mentioned above, at least the macro level but this is also very limited, simply forget that option, it's not worth the try). Even the video shows how to implement a button into the search form with an assistant - yes, you only need to click a little bit, but in the end you have a (not very good) code as macro or as VBA which the assistant creates - and this only does the first step, it creates the code it can create, but it is not able to change it later, so you need to look into the code yourself and change it to fit your needs. So sooner or later you will not come around to program something. You don't need to be a star programmer to do that, most easier things can be done with some simple commands. But the better you want to have your frontend the more you need to learn VBA, and the better you want to have a good performance and good and easy queries the more you need to learn the details of SQL - a lot of things cannot be made with the query designer and can only be made by writing SQL manually. Switching to the query designer in that cases would only end in an error message saying that this SQL cannot be represented graphically, so you see nothing and must stay on SQL level - that's also why you should really learn it and don't be dependent on the graphic designer.
And really, a simple command like
SELECT Field1,Field2,Field3 FROM MyTable WHERE Field1 LIKE 'A*'
is really nothing which any English speaking person would not understand without knowing SQL, isn't it? A lot of queries are nothing more than that.

But to get back to your initial question: There is a similar way to the video to create a form which is handled by the developer but has also comfortable filters included: The datasheet view.

If you create a form which is based on a table (better here: always use a query as form RecordSource) and set the "Default View" setting to "Datasheet" (and of course also "Allow Datasheet View" to "Yes") then you can simply add the desired fields of the table/query to your form in design mode (doesn't matter where you place them) and open the form - you see a form which looks exactly like in the video, like if you opened a query or table directly. But the difference is: You are now working with a real form and that means, you have full control over anything what happens here, you can use Conditional Formatting, you can (in a limited range) format the fields, you can use events to catch what the user is doing, you can fully program it and so on.
Additionally, you already have a complete search form here: Any header has a little triangle which opens a comfortable filter menu which can do a lot more than the very primitive search form of the video, in case of date/time fields you have even additional filters like "all from year 2015" and so on. You also have an input field to enter search criteria, like "begins with" or "ends with" and so on. Additionally you also have the possibility to sort the selected column.

Not enough or you do not want to use a datasheet view? Why not use the Access integrated filter options? You can add the filter buttons (since A2007) to the Quick Access bar using the Access options, you'll find the filter commands there. There are advanced filters like "filter by selection" where you can mark a text in the current list and click this button to find all records containing the marked text in the same column. Or you can use the very advanced "Filter by form" filter (caution: only possible in the full version, not the Runtime) where Access automatically creates a form based on your form design but any input is handled as filter string - with tabs in the footer where any click creates a further duplicate of the same form to enter more values - this one can create very complicate filters.

If that's not enough, then you need to start to program one by yourself, like the one of Helen.


Al Jr GriffenAuthor Commented:
I am awed and very much thankful by the time your experts have given me (all 3 of them) . It will take me a bit to digest what they are saying. I am hopeful that my problem gets solved as well. I need to know how they connected the user input for 4 unbounded variables that they placed on a form in such a way to have them manipulate a query like a flexible filter. In the video, each of these variables was able to be specified (all or in part) causing the query to be sorted/filtered accordingly.  How this happens without code is what I need to know (not obvious to me in the video). Looks like Magic, but I know there must be a way that I too could create one of these for my needs.   Let me know if this is too tedious a question.  I have the application of an out of the box A2010 solution in mind, rather than code, (just because the author of the video seems to be able to do it, and as of yet, I am not able to replicate this seemingly doable task). Can you specify what the steps are to complete building this form used as filter. He, in key spots does not.... leaving an Access 2010 application mystery for me to unravel.
Al Jr GriffenAuthor Commented:
digesting feedback. Will comment again. Thx again for the feedback.  -ag

this can be done like this (only one possibility):

WHERE 1 = (Iif(Nz(Forms!MySearchForm!SearchField1)="", 1, 
			  (Iif(Nz(MyField1) LIKE "*" & Nz(Forms!MySearchForm!SearchField1) & "*", 1, 0))))
OR    1 = (Iif(Nz(Forms!MySearchForm!SearchField2)="", 1, 
			  (Iif(Nz(MyField2) LIKE "*" & Nz(Forms!MySearchForm!SearchField2) & "*", 1, 0))))

Open in new window

In general you need to switch between checking if the search field is empty, then return 1 (which means "1=1" which is True) and checking if the database field is like the filled search field with automatic adding of "*" at the beginning and end, if yes, return 1 (1=1 again) if not, return 0 (1=0, False).

The shown method of course only works with text fields, if you have dates or values you would not use LIKE and "*", but the way is the same.

But caution: This would work, but it destroys any possibility of using indexes which makes this kind of search only interesting for the lazy programmer, but will not work very fast.
If you want to have a fast search form there is no other way than assembling a correct WHERE-string in VBA using the input fields and using that filter string in the query.

Also, the use of Nz often means that the original datatype of the table changes so the results are not always what you thought it should be.


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To see how this works in the video you linked, look around 8:30 in the video. The author shows how this is accomplished in the query, by directly referencing the Form as I suggested earlier:

In the criteria row, you'll see statements like this:

LIKE "*" & Forms!YourFormName!YourControlName & "*"

That's how the two are "wired" together. For each column in your query that you want to search, you'll need a corresponding Control (i.e. Texbox or Combo) on your form, and your query's column will be associated with that Form control. Essentially, the Criteria row in the query design grid "tells" the query how to filter that specific column.

Again, this is NOT the best way to do it, for the reasons Chrisitian and I have already mentioned.
Helen FeddemaCommented:
For a simpler query form, see my Simple Filters sample database:

Simple Filters form
I learned how to program in 1968 using COBOL on an IBM 360 mainframe.  This was a multi-million dollar piece of hardware with less computing power than a modern calculator.  I've written my million lines of code and I don't need the practice and that is why I love Access so much.

If you can do it with a property setting do that.
If you can do it with a query do that.
Otherwise, write code.

I don't write code just because I can.  For bulk updates, queries are usually more efficient anyway.

Helen has provided two excellent examples.  The number of search fields and the complexity of the connections dictates my method, I either create a custom WHERE clause using VBA or I simply reference form fields from queries.  For cascading combos, I always reference form fields.  Combo2 references combo1.  Combo3 references Combo2, etc.  The AfterUpdate event of each combo, requeries lower level combos and clears out their values.

AfterUpdate for Combo1 (in 3-combo set)
Me.Combo2 = null
Me.Combo3 = null

AfterUpdate for Combo2
Me.Combo3 = null
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Pat - this one's nearly a year old! Did you get a notice about it? I had hundreds of them today ...
Hi Scott,

me too... nearly every minute an old notice... maybe someone should inform the EE team about that, I didn't do that because I guess they must have been informed through a lot other Experts already...;-)


Hi Scott,
I did.  Maybe they're running a sweep of all unclosed threads.  I didn't even notice the date.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.