Form Search query not limiting results due to null values in table?

[THarrison]

I have a search form that I am building in Access 2007 with a button that uses a query to search a table for matching values. The search form has 24 different fields the user may use to search the table depending on the information they have (i.e. customer name, account number, state, phone number, etc). It may be important to note that some fields in the table have blank/null values. I would like the search form to return the results based on what values the user enters. For example, if the user does not enter any search criteria, all results are returned, however, if they enter the customer name, the query only returns the records that match such customer's name (preferably a "fuzzy" search here). I have set up a query using the following SQL's (not at the same time, of course) in each field criteria box:

Like [Forms]![Form Name]![Corresponding Field Name] & "*"


Like [Forms]![Form Name]![Corresponding Field Name] & "*" Or Is Null

The form, itself, works fine and runs the query. However, when I click the search button using the above SQL's, the query that runs returns all results, even if I have search criteria trying to limit the results. I understand why the second SQL returns all results, since you're essentially telling Access to return all results that have a field matching a "Null" value. I am not sure why the first SQL doesn't work...

Does anyone have any advice as to how I can get the query to function correctly based on the search criteria entered by the user? Is there a better SQL to use?

Also, is there a good way to set up the SQL so that it searches multiple columns in the table. For example, if a user enters a phone number in the search form, the query would search both the phone number 1 and phone number 2 columns for matching results?

[rpeare]

If you want to build your SQL statements on the fly you can do pretty much whatever you want. If you want to have a 'static' SQL statement and you are search text fields you probably want

Like "*" & forms!Formname!Fieldname & "*"

this will show you any record that has the string anywhere in the field. In your current search you are only searching for text values at the BEGINNING of the field

so for instance if you have a last name field with the values

Arrington
Barry

and you search for ARR

your current search will only find ARRINGTON and ignore BARRY where the double wild card syntax will capture both.

[orange]

There is a free video on simple search form here.

[THarrison]

Thanks rpeare. I did try to use that SQL as well, however, I still run into the same issue. It seems to me that Access is including a record in the search results if any of the criteria in the table is null. For example, let's say I have two entries in my table ABC Contracting and USA Electric. In the table, it contains values for the company name, street address, city, state, zip, phone 1, phone 2, balance, branch, region, fed ID, etc. When I try to search for "ABC", the search results still return USA Electric also, even though USA should have been excluded since I am searching for a company name beginning or containing "ABC". I am not sure if it is because I have null values for USA Electric in the table under the phone 2 and fed id fields. On the search form, the state, branch and region fields are all combo boxes. I am not sure if that would mess something up.

[rpeare]

can you include a sample database with enough junk data in it to replicate the problem. This doesn't sound like an issue with nulls, more like a question of having criteria in the right place.

EDIT
that is if you have removed the criteria 'or is null' from your query

[THarrison]

Ok....if I did it correctly, the sample database should be attached.

[orange]

I looked at your database quickly and tried the search. It is quite slow in searching. You need to put indexes on some of the fields you'll be searching. It should speed up processing.
Watch this video to see the difference between indexes and no indexes.

You really should avoid names with embedded spaces -- it will save you from some syntax errors.

Every table should have a primary key.

[THarrison]

Orange, I did watch the video earlier and meant to actually index some of the fields to allow for quicker processing. If I am allowing for people to search using all of the fields, should I index all of the fields?

Also, is there a way to add a primary key after the table is created?

[rpeare]

Well you have a few problems with this

1. You are using a bunch of spaces and special characters in your field/object names which is really going to make things harder on you if you want to have a space in a field/object name use an underscore (_), don't use /, #, or any other character other than letters in your field names, it will save you tons of agony.
2. You are using the 'like' operator on numeric fields which will not work
3. You are using the 'like' operator on a date field which will not work
4. You are opening a report based on a table, not the query
5. After some experimentation with your query I have come to the conclusion that even though it is not throwing an error, it is not able to handle as many criteria as you want.

I have modified your database, given you a new form which applies a filter to your report based on whether or not fields are filled out.

Account Database.zip

[rpeare]

Another good point by orange, you should have a primary key (autonumber field) in every table you design it will help you reference specific records easily and will generally speed things up.

[orange]

Are there only 2 records in the database? Or is there an issue with removing a filter or similar?
Indexes may not help if you do searches using the * & searchterm & *

That approach doesn't work with numbers as rpeare has said.

You can add a new field as PK.

Good luck with your project.

[THarrison]

rpeare, thanks again. I think you created exactly what I am looking for. I appreciate you adding your notes to the VBA to let me know what you were doing also. I went through my database and removed all the spaces from the Form, Table, Report, Query and Field names. I also added a new field for the primary key.

I am still having problems in my form though. I copied over your VBA to my DB (I also added all the tags to the property fields as you had them and corrected references to the forms/tables/queries/reports names) however, when I run the search, it comes up with the following error:

"Syntax error (missing operator) in query expression '[custname] Like '**' AND [dba] Like '**' AND [custnumbs] Like '**' AND [compadd] Like '**' AND [compcity] Like '**' AND [compzip] = Like '**' AND [ownername] Like '**' AND [balance] = AND [phoneno1] Like '**' AND [owneradd] Like '**' AND [ownercity] Like '**' AND'."

When I go to the debug screen, it highlights the "DoCmd.OpenReport "CLAcctInfoTableReport", acViewPreview, , Left(sWhere, Len(sWhere) - 4)" line. I double checked to make sure I wasn't missing any property changes but couldn't find anything. Any idea what would be causing this error?

Also, I took note earlier that you said that Access won't allow you to use the "Like" operator for numeric fields. Does the filter you added ignore this type of error?

[THarrison]

Orange, to answer your question, this database only has two records right now because I wanted to make sure it worked correctly before myself and the users entered the rest of the data into the database. There will eventually be hundreds/thousands of entries into this database.

Also, you may be able to answer this question. I tried to build a "Clear Form" button into the forms. I originally tried to do this with a macro that closed and reopened the form. Then I saw the way it was done in the video you posted and tried to use the VBA that was contained in the video. However, when I tried to run the operation using both methods on the "Add form", it still added the record to the table. Any idea why it does that? Is there something I am missing?

[rpeare]

If the code I gave you is generating that huge string something is wrong. A field should only be in that filter string if the value of the field is NOT null (non zero length) which tells me your statement is not evaluating correctly. NOTE your the 'new' form I gave you has field names that exactly match the fields in your table, the string you are posting does not, for instance your field name is 'custname' where on your table it is [customer name]. For the code I gave you to work the fields on the form have to be named exactly as they appear on the table.

Secondly, the search fields have a D for date, T for text or N for number in the TAG property, the search string is built based on what you have in the TAG property, in your case I have 'date added' with a tag of D (date), company zip and owner/principal zip and balance with a tag of N (number) and everything else with a TAG property of T (text) the code then figures out the appropriate syntax for the filter string. (See the select case statement)

To clear your search items

dim ctl
    if len(ctl.tag) >0 then
        ctl = null
    endif
for each ctl in me.controls

next ctl

this is making use of the fact that you have your 'search' fields with TAG property that is non zero length to clear them out.

[THarrison]

I figured that the references had to be the same in order for the search to work properly. I went thru the entire database and took out all the spaces for the field names (as you and orange previously recommended). I then made sure each field (in each form, table, report and query) was referencing the correct field names.

I tried running the code with all fields empty (to return all results) and with search criteria (customer name) filled out to try to pull specific results. I received the syntax error both times.

Also, the clear form code that you included returned an error also. The error that returned for that was "Run-time error '424' Object Required".

I have included an updated version of the database so you can see what I am seeing. Account Database - Updated.zip