Query design view - commas being changed to pipe characters

[ruthie2012]

This is very strange. When I build an IIf expression in query design view
access (2003 first, and then upgraded to 2007) is changing the commas to pipe
characters. I then get 'invalid use of pipe character in query expression'.
If I change the pipe characters to commas I then get 'the expression you
entered contains invalid syntax'.

I can enter the expression in sql view and the query will work, but if I go
back to design view I get the 'invalid pipe character' error and it won't let
me go back to the sql.

If I use the expression builder it gives the IIf template as IIf («expr»|
«truepart»| «falsepart») - i.e. it put the pipe characters in instead of
commas.

Also I can't typre the expression directly into the Field row in query
design. I get the same syntax error.

Even weirder - this is only happening on one pc. My other one (same spec) is
fine. Help!!! Any ideas?

 

[John Spencer]

JUST A GUESS

It sounds as if your OS regional settings have been modified so that the List
Separator character is now a pipe symbol instead of a comma.

Controls Panels: Regional and Language Options
Regional Options Tab
Customize ... Button
Number tab
Check the value of list separator

JUST A GUESS

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ruthie2012]

Exactly right! Thank you very much!

JUST A GUESS

It sounds as if your OS regional settings have been modified so that the List
Separator character is now a pipe symbol instead of a comma.

Controls Panels: Regional and Language Options
Regional Options Tab
Customize ... Button
Number tab
Check the value of list separator

JUST A GUESS

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

OH YEAH!!!! I love it when I make a wild guess and things turn out correct.

Dance Dance Dance
Dance as if nobody's watching
Dance Dance Dance

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ltam1015]

Thank you, thank you. I encountered this same problem with commas in my query and initially thought it was an ACCESS library problem. Upgrading from 2007 to 2010 did not solve the issue. I googled the problem and got this suggested fix. It worked. Thanks, again.