weird problem when using MS Query from Excel

[Steen Persson (DK)]

Hi

We are having some strange problems with SQL statemnts when users want
to edit a database request in MS Query.

First the user go to "Import External Data - "New Data request" (I don't
know the exact name for this since I haven't got an English Excel at
hand), select the source and then get into the selection guide. Here the
user just select a table and exits and get into MS Query. He then add
a couple of criteria - a number "Greater Than xxx" and "Less than xxx".
He then returns the result to Excel and everything looks fine.
When he then go back and wants to edit the selection, he get and error
like "Incorrect syntax near 'Og'. Statement(s) could not be prepared".
When I then look at the SQL statement, I can see that it translates and
"And" og "og" (which is the danish word for And). Everything else is ok.

If I look at the SQL statement before I return to Excel it looks like this :

SELECT FinSelskab.FinSelskabNr, FinSelskab.FinSelskabNavn
FROM DRIFT.dbo.FinSelskab FinSelskab
WHERE (FinSelskab.FinSelskabNr>10000) AND (FinSelskab.FinSelskabNr<20000)

- which looks fine.

When I then return FROM Excel and get the error, the statement now looks
like this :

SELECT FinSelskab.FinSelskabNr, FinSelskab.FinSelskabNavn FROM
dbo.FinSelskab WHERE ((FinSelskab.FinSelskabNr>10000 Og <20000))

- and here it has translated "And" to "og".

We are runnning Excel in Citrix and there are a number of users who has
the problem, and at least one who doesn't. We've tried it on different
Citrix servers, but it doesn't looks like it's server related, but more
related to a user setting somewhere - maybe in the users profile.

I've looked high and low for a solution, but I'm starting to run out of
ideas.....

Regards
Steen

 

[Philip]

what langauge is the ODBC driver? Is it the Danish version...or is it a
strange non-ODBC driver?

It must be something to do with the regional / language settings of the
workstations, have you tried it on a machine that uses English as the system
language?

The best option is to programmatically set the VBA instead of using the Data
Wizard in Excel...

Create a simple query against the db using the MS query wizard, then create
a button or menu item that asks the users for the criteria you want, and
programmatically modify the query table objects (worksheetname.querytables
...) -
maybe that will be the solution

HTH

Philip

 

[Steen Persson (DK)]

what langauge is the ODBC driver? Is it the Danish version...or is it a
strange non-ODBC driver?

It must be something to do with the regional / language settings of the
workstations, have you tried it on a machine that uses English as the system
language?

The best option is to programmatically set the VBA instead of using the Data
Wizard in Excel...

Create a simple query against the db using the MS query wizard, then create
a button or menu item that asks the users for the criteria you want, and
programmatically modify the query table objects (worksheetname.querytables
..) -
maybe that will be the solution

HTH

Philip

Hi Philip

I'm not sure that it has anything to do with the ODBC driver, since it
seems to be user dependent -i.e. it works for one user where it fails
for another one even though they are connected to the same Citrix server.
I've also looked in to Regional Settings etc. but I can't really see
that it can be anything to do with that since it's Danish versions all
over.

Actually we've went a bit further. I can see, that when the user select
"Edit Database Query" (..or what ever it's called in an English version)
and then use the wizard so edit/select the criteria, it's already here
it fails. When I select the column where I have my criteria, I see the
operator "is greater than" and then in the value list to the right it
says "10000 Og <20000" which is wrong. It should rather show "Is greater
than" and "10000" and then in the box below show "Is less than" and
"20000". Apparently it's already here it does it wrong.

I don't know if this makes more sense? I can't really see that the user
is doing something wrong or something that shouldn't be supported, and
also it's strange that it works for some users.

We've also tried to test it with an older version of Excel (XP) that are
installed on one of the Citrix servers, but that gives the same error.

Regards
Steen