Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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!

share|improve this question

migrated from stackoverflow.com Apr 3 at 0:11

This question came from our site for professional and enthusiast programmers.

    
Is there an example database you can upload somewhere and make available that demonstrates the problem? –  mellamokb Apr 2 at 17:58
    
What if you search on the pipe character | in the Excel sheet? –  Smandoli Apr 2 at 18:01
    
No form of searching in Excel found the offending "|". : / –  user3490508 Apr 2 at 18:02
1  
You've added tons and tons of text, but no actual details, sample data, or any code that will allow us to research or identify the problem. What you've basically provided is a description of an automobile to the mechanics and then said "And it makes a funny noise when I turn left" without giving any information about where the noise is coming from, the exact make and model of the car, or the road conditions at the time. I doubt you'll get much of an answer about how long to repair it or what it will cost based on that information. Sample data, code, and specific details are going to be needed. –  Ken White Apr 2 at 18:05
1  
Please describe the method you're using to apply the filter expression. –  HansUp Apr 2 at 18:14

1 Answer 1

We had the same error message and the solution was to adjust the date format in Windows (control panel/regional settings) to be the same as the table's short date format was defined. In our case the table had this field date format defined: yyyy.mm.dd (when I showed it in design view) and in Windows(control panel/regional settings) we had 'yyyy. MM. dd'. I noticed there were spaces in this setting in front of month and day parts, so I opened down the list, selected the top most one (yyyy.mm.dd - without spaces), then applied the settings, I went back to Access, even I did not close it, just re-opened the table and I was able to filter by selection. Good Luck. Kr, Balazs

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.