the expression '|' you entered is invalid

[skippy]

Hi,

new to Access and brandnew to this forum. I got an odd error and this is one of very few in my life I couldn't find any solution by asking Mr. Google. It seems that Access itself puts the invalid part into the sql, but I can't find the place where I can prevent from doing that.

What I have:
Access 2013 64 Bit on Win7
What I do:


simply filtering any column in the datasheet view. When I do just one selection, it works. If I select more I get an error. This seems to apply only to columns with more than three different values.
What I get:
an error form thrown by Access, saying the expression '|' you entered is invalid

I can do the required filtering using the sql itself or the design view, but I do want to provide the filtering.

Hope somebody can help here, any hint highly appreciated.
TIA

[ranman256]

what did you type in for the filter? Did you type in the '|'?

[skippy]

No, didn't type anything. Just used the dropdown fields of the columns and selected by checking the checkboxes:

[skippy]

And what I get is this:


I have just created the database above completely fresh and with Field1 and Field2 I can select different values, but I cannot with Field3 as there are four different values (three for field1 and field2). If I change the last "A" Value for Field2 to something different, the same happens there.
I can provide this sample DB (you can also do it in < 1 min) if required.

[CJ_London]

in your table design, is your field3 a calculated or multivalue field?

[skippy]

all just short text. for the example I just started access, table1 was created automatically and typed in some values.

[CJ_London]

it was just a thought - they can cause issues like this.

Have you saved the table before trying to filter?

If still a problem and you are just exploring, suggest start a new db and try again, you may have 'tweaked' something unknowingly

[skippy]

saved yes.
I saw the problem initially when I imported an excel sheet, so I wanted to exclude any possible side effect, so I did exactly what you suggested, just created a new one and did nothing than these three fields with some value.
Below the DB (very small) maybe someone can check if he has the same problem ?

Database21.accdb

[CJ_London]

just tried it and works OK for me (I'm running 2010)

[skippy]

Got it :-)
As it was obviously not the database as it was working for you, I checked some system properties. I found that the list separator was '|' instead of ';'. Changed it, tried again and worked !

Great. Thanks for your help.

[ssanfu]

FWIW,
Using Win XP/A2010 32bit.
I downloaded your dB.
Set the filter as you described - no error
Added more rows - still not able to reproduce the error.


Edit... late again browser didn't refresh......

[Michael3876]

After quite some testing I found out that the problem seems to be as follows: as soon as there are more then 3 different values in a number field (no matter which type) the error occurs. If you change back the 4th value to one of the three the filter is okay again. So this seems to be a major bug in MS Access!!! I'm using the newest version of Office 365, so that is Access 2016, I guess!