"All/Integrated" generates an error message when being used as criteria in filter by form operation

[Hagridore]

I have a database with a master table with 34 fields for each record. I know the advantages to creating my own search form, but I've been told by folks what am in charge not to bother...they're okay with filter by form. Any of these 34 fields may be needed as criteria to find records. The overall process is working fine. I have several controls that are using listboxes connected to secondary tables to accept multiple values for input, some of which are stored as multiple values and others that enable multiple values for the search but only store single values in the record itself. One of my tables has the specific value "All/Integrated" (without the quotes) entered into a field. This single entry is causing problems. I thought it was because of the /, but this is not the case. I created a record with the value "Something/Whatever" (without the quotes) in it, and did not get any error message, and tried using "All/Integrated" (no quotes) in another database and got the same error message. This is the message I'm getting:





Is there some reason Access is treating "All/Integrated" differently than "Something/Whatever"? Again, there is no subquery being run and no secondary code to be displayed or shared. Access doesn't even THINK there is one unless I use the specific value "All/Integrated" in the search. Though I can't be more specific about my form or table because of my company's...p...no...high level of concern...about privacy, I can come up with another database to share that mimics what I'm doing and when this error message comes up if it would help.

[June7]

You are using dynamic parameterized query? Post the query SQL statement.

I NEVER use them.

[Hagridore]

Hmmm...I haven't written any SQL statement. I'm just using filter by form with the listbox functioning to pull up a value which is used as a search/filter criteria. And, as I said, it's working in every other situation EXCEPT when the specific value "All/Integrated" is selected. The question is is there any reason why Access is treating "All/Integrated" differently than any other criteria, including another slashed criteria like "Something/Whatever"?

[June7]

I haven't used filter by form.

The values available for selection come from the records - right?

So there must be records that have the value "All/Integrated"?

[Hagridore]

Yes, there are records which use that value. And I even ran the filter after adding "Something/Whatever", using that as a criteria before I added a record that had it. The result was the blank that normally shows up when the filter results in no records that match the criteria are found. Then I added a record with "Something/Whatever" in it and the filter by form process found it exactly as I wanted it to. For some reason, it appears that Access treats "All/Integrated" differently than anything else.

[June7]

Would have to examine data and db structure. If you want to provide, follow instructions at bottom of my post.

[Hagridore]

Would have to examine data and db structure. If you want to provide, follow instructions at bottom of my post.

Alright, here's an example that ends up having the same issue. Note that choosing any of the criteria in "Department" as filter criteria results in a sample from the Faculty table, except for "All/Integrated". The "New Search" button runs filter by form using the primary (only) form. I hope this helps. Thank you for your efforts!

[June7]

First, you are using multi-value field for Dept in tblFaculty. I NEVER use them. https://support.office.com/en-us/art...C-6DE9BEBBEC31

Second, I've never used filter by form. Review http://www.allenbrowne.com/ser-62.html

There is definitely something odd about the All/Integrated string. I changed the value to Al/Integrated and edited the records. This value works. So I tried All_Integrated. That also works. I tried Amm/Integrated. That also works. So there is something about the double 'l' followed by /. Maybe you should use Integrated/All. That works.