S
Steen Persson (DK)
Hi
I'm having a strange problem when some of our users are running queries
from Excel. They create the query in MS Query using the GUI - select
tables etc. and then enter their criterias. They can run the query and
return the results to Excel - no problem.
When they then want to edit the query, and in Excel choose "Edit
database query" (I'm not sure what the correct naming for this is since
I've only got a Danish Excel at hand..) they get back to MS Query and
then they get this error message :
Incorrect syntax near '#'
The statement could not be prepared
I think I've narrowed the error down to being a date range selection
containing an AND that are causing the problem.
When I look at how the query looks as a SQL statement - before returning
data to Excel, it looks like this :
SELECT .....FROM MYTable WHERE....((BudgetPos.BogforingsDato>{ts
'2005-06-30 00:00:00'} And BudgetPos.BogforingsDato<{ts '2006-01-01
00:00:00'})......
This looks ok and it also works as expected. If I then look at the same
query after returning to MS Query (where they get the syntax error), it
looks like this :
SELECT .....FROM MyTable WHERE...((BudgetPos.BogforingsDato>#30-06-2005#
Og <#01-01-2006#)) ....
As you can see, it has now changed the AND to an "OG" (which is AND in
Danish) and it has put the # around the dates instead of {ts.... We
filter on other fields in the query and between the various fileds, it
shows AND as it should and doesn't "translate" to "Og". In other words
it seems like the problem is only with the daterange in one field.
To me it looks like the date criteria now looks excatly like how it's
being entered in the "criteria box" in the GUI in MS Query.
When I run the same selection in my own Excel, it works fine - same
saved selection etc. works without any problems.
When I look at the SQL query after returning from Excel it looks the
same as before so for me everything works as expected.
We are running Excel in Citrix, and I run it off the same servers as the
regular users, so it's the same Excel/MS Query installation.
I've looked high and low to try to find out why we see this problem, but
I simply can't find any reason.
Regards
Steen
I'm having a strange problem when some of our users are running queries
from Excel. They create the query in MS Query using the GUI - select
tables etc. and then enter their criterias. They can run the query and
return the results to Excel - no problem.
When they then want to edit the query, and in Excel choose "Edit
database query" (I'm not sure what the correct naming for this is since
I've only got a Danish Excel at hand..) they get back to MS Query and
then they get this error message :
Incorrect syntax near '#'
The statement could not be prepared
I think I've narrowed the error down to being a date range selection
containing an AND that are causing the problem.
When I look at how the query looks as a SQL statement - before returning
data to Excel, it looks like this :
SELECT .....FROM MYTable WHERE....((BudgetPos.BogforingsDato>{ts
'2005-06-30 00:00:00'} And BudgetPos.BogforingsDato<{ts '2006-01-01
00:00:00'})......
This looks ok and it also works as expected. If I then look at the same
query after returning to MS Query (where they get the syntax error), it
looks like this :
SELECT .....FROM MyTable WHERE...((BudgetPos.BogforingsDato>#30-06-2005#
Og <#01-01-2006#)) ....
As you can see, it has now changed the AND to an "OG" (which is AND in
Danish) and it has put the # around the dates instead of {ts.... We
filter on other fields in the query and between the various fileds, it
shows AND as it should and doesn't "translate" to "Og". In other words
it seems like the problem is only with the daterange in one field.
To me it looks like the date criteria now looks excatly like how it's
being entered in the "criteria box" in the GUI in MS Query.
When I run the same selection in my own Excel, it works fine - same
saved selection etc. works without any problems.
When I look at the SQL query after returning from Excel it looks the
same as before so for me everything works as expected.
We are running Excel in Citrix, and I run it off the same servers as the
regular users, so it's the same Excel/MS Query installation.
I've looked high and low to try to find out why we see this problem, but
I simply can't find any reason.
Regards
Steen