Issues in Multi column Search

[Anil Bagga]

I have a Table of about 3000 items. The table structure is given in the Design View in enclosed file. I want to set up a query on internal code, and Group/SubGroup category of the items. For this I created a form with text fields and a query in line with the video below



https://www.youtube.com/watch?v=CTiA_4Me0cI

The structure of the forms and the criteria is shown in screenshots in the enclosed file and the criteria of 2 fields is as below (just in case it is not clear in the screen shots). What I want is that either or all search fields must work - meaning even I put one criteria and leave others blank, query must give records of all items which fulfill this one query of one field. If 2 fields are used, then result should be of 2 fields in AND condition. Partial text like Fab for Fabric should also work

Like "*" & [Forms]![ItemSearchfrm]![txtGroup] & "*"
Like "*" & [Forms]![ItemSearchfrm]![txtSubGroup1] & "*"

The query works in a strange way. I just put Bag in Group and I get 60,000 records and all with one particular combination of Sub groups. Even if I put values in the form for sub groups, these are not recognised. Screen shots of the form and the result of the query are also part of the screen shots in the file

Is my approach right?
What am I doing incorrectly?

[June7]

I don't use dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html

Especially since users should not work directly with tables and queries, only forms and reports.

Without knowing more about your db structure, hard to say if what you are doing is correct. If you want to provide db for analysis, follow instructions at bottom of my post.

GROUP is a reserved word and should not use reserved words as names for anything.

[Anil Bagga]

Thanks. I am pretty new to Access. It will take me some time to digest and follow this. If I run against a wall, will share the db

The query I have set up delivers results but it seems the filtered results are not cleared from cache and the next query uses the filtered results of the previous query. Is there a way to clear the query cache every time we run the query command?

[June7]

Not sure what you mean by 'query cache'. I don't see how what you describe can happen with dynamic parameterized query.

[Anil Bagga]

What I meant was that if I use one condition say A, to filter the table with 3000 records , I get 1000 results . I then close the query and DB. On re opening the DB on a blank form of the page I put a new query B on another field. The results are then searched from these 1000 previous results, and not from the 3000 records of the table!

[June7]

I never experienced that. Would have to review your db.

[Anil Bagga]

DB enclosed. It has only 1000 lines of data. With no field values in the form and with filters in the tables on groups/subgroups, one gets different results

[June7]

I am not getting issue.

Provide specifics. Exactly what should I enter to replicate issue?

[Anil Bagga]

The first thing is that without any search criteria in the form, the number of records, after pressing the query button on the form, should be same as the no of records in the table. They are not

Next if you filter the table and note down the number of records where Group is Bag and Fabric, and then use the same criteria in the form - by putting bag and fabric under group, you will see the numbers are very different. The same thing is replicated if we compare the no of records in table filter and the query if we use subgroup 1,2 etc

I think if we can find the reason for the blank form and Group field variance, we would be able to solve the Subgroup variance also.

[June7]

Reason for result are Null fields. These are ignored by wildcard filter criteria and records are not retrieved. Review the Using a query instead section of the Allen Browne tutorial I referenced.

Not source of issue but I notice there is no criteria under subgroup 4.

[Anil Bagga]

Allen Brown article :I will try that. It does not seem to be easy

The strange thing is that when I queried the first time on a blank form, after building the database I sent to you, all 1000 records showed up. So if the wild card characters were being ignored, how did this happen?

Once i did my first query on Fabrics the no of records was exactly as per the table. I dont remember the number but I tallied it. Then when I started adding more fields in the query for additional filtration, the no of records naturally started reducing. Until this time it was fine.

Then I reverted back to a query on a blank form and the number of records were not the same as before. They were lesser. It was almost as if the previous query parameters had not been removed from the cache. This is the reason I mentioned clearing cache after each query! This is disturbing as this was not logical and in whatever I have read, it should work!

[June7]

Wildcard characters are not ignored, the fields with Null are. The additional fields you added have Null for some records. Just as a test, remove criteria from those fields that have Nulls - what happens?

Your query would work as is if every field had a value for every record - no Nulls.

Another way to handle this is to deal with Nulls. Calculate fields in query like Nz([SubGroup3],"NONE") and apply filter criteria to this field.

[Anil Bagga]

Thanks June7. That solved the issue. You have been a great help

Thank you once again

[CarlettoFed]

Here you can see an example of a dynamic filter.
In the combo box controls, the search can be performed exactly if the value is selected from the existing ones, or partially if the value does not match.
The Remove Filter button clears all search controls.

[Anil Bagga]

Wow.That's a very professional job and beyond my imagination. To take this pain is quite extraordinary. Hats off.

No words to express gratitude. It will take me quite a while, to understand this looking into my status as a novice. I will use this to improve my learning.