This a very odd problem and I am hoping that someone here can figure it out.

I get this error when working in a database and trying to FILTER a table by column and more than 1 value. “The expression ”|“ you entered is invalid.”

For instance, if I want to filter the table by column B using X and Y as filter options. It will filter fine if I only select X but, errors when more than 1 filter option is selected. This happens if you are in one of the basic tables and it also happens when you have the table being called upon in another form page.

The extremely odd thing is that this particular database has about 20 backups from different periods of time, over the last few years, and the error has proliferated through all of them even though some have never been opened and saved since the error began occurring. It also proliferated across all tables inside the db(even ones that have not been changed in a long time.)

So here's what happened when I noticed the error (and I think maybe the root cause). I was adding rows of data to the bottom of one of the tables(in MS ACCESS) and pulling it from an MS Excel column which was a column with a concatenate formula generating the value from other columns. It is my theory that it copied the formula from Excel and pasted it into Access but looks as if it pasted the Value. Almost like non-printing characters in a Java Script. I pulled the entire table into Excel and found no "|'s".

I have noticed a few things: 1.I deleted the table rows that were copied into Access and then manually input the data with no success. 2.I created a blank table in the same DB that DOES NOT throw the error if only new data is added manually but WILL throw the error if a row or column from the older table is pulled and copied into it. Even if the copied data does not include the newer information that I feel messed it up. (This makes no sense and makes me think that one of the column headers has an expression linked to it somewhere else in the database. Only problem with this is that the error persists even using new column headers.)

It almost seems as if the filter itself has had some sort of change made to it.

Any help resolving this issue would be greatly appreciated. Please note that I am an extreme novice, when it comes to database, so please make answers understandable to that extent. Thank you, Thank you, Thank you!

I had posted on StackOverflow.com with little answer. Here is the link to the posting there: https://stackoverflow.com/questions/22819291/ms-access-2010-the-expression-you-entered-is-invalid

Here is the skeletonize DB (I believe all sensitive data has been removed, if you find any, please delete as it is Federally protected data.) https://drive.google.com/file/d/0B69UIeGwPa5FcF9SV2FPWnhuTms/edit?usp=sharing

  • In the Stack Overflow version of this question, you reported the filter error problem should happen with the EmployeeName column of the Employee Table ... or with a form which uses that table. However I can't reproduce this problem in Access 2010 with your "skeleton" db. Does the error still occur on your system with the skeleton db? – HansUp Apr 2 '14 at 19:51
  • Yes, I downloaded it from my google drive just to make sure it errored out. Hmmm, this is strange. I say this because when I open other DB that I have built and use, this error does not occur there. – user3490508 Apr 2 '14 at 19:54
  • I just tested it again and it continues to throw this error. Thank you for the input. It is a strange issue. – user3490508 Apr 2 '14 at 19:58
  • In the backstage view, I get an error message complaining about Custom UI XML of Microsoft Access Package Solution Wizard 2010 COM Addin. Do you get that message? Perhaps that add-in interferes with the filter behavior. Do you need it? – HansUp Apr 2 '14 at 20:02
  • This is not ringing a bell. I don't believe I added any custom UI on the tables. I do not exactly understand what "backstage view" is either. As I stated I am an extremely novice user and I fought tooth and nail to get the DB setup in the first place. Thanks for your help in this. – user3490508 Apr 2 '14 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.