Access generates this error when it can't parse a query. There can be lots
of reasons.
Given that it works on some machines and not others, we will assume that
it's not merely something like misplaced brackets.
If the query contains any parameters (i.e. things you supply at runtime),
such as:
[What Date?]
or
[Forms].[Form1].[txtStartDate]
you may be able to solve the problem merely by declaring the parameters, so
Access understands the data type. In query design view, choose Parameters on
the Query menu. Enter them into the dialog, one per line, and make sure the
datat type for each one matches the data type of the field you are applying
the parameter to.
Another possible cause is any expression. Again, typecasting the expression
can give Access the information it needs to run the query. More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
If that doesn't solve it, you are starting to search for what's different on
the machines where the query fails. Locate the file msjet40.dll (typically
in windows\system32), right-click and choose Properties. On the Version tab,
you should see:
4.0.xxxx.0
The minor version number (first of the 4 missing characters above), should
be an 8 or a 9 (depending on your version of Windows.) If it is a 7 or less,
you need to apply SP8 for JET 4. Download instructions:
http://support.microsoft.com/kb/239114
You could also check your version of Office, to make sure you have the right
service pack. Updates:
http://support.microsoft.com/sp/
If the service packs are all up to date, there may be a difference in
regional settings that is causing a date, delimiter, currency, or other part
of a field to be interpreted wrongly. This article explains how to handle
dates:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
There are also possible issues with security (blocked functions, etc), but
normally they should generate a different message.
Hope that's enough to let you track this down.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
rascal said:
I have an Access 2003 database that I am using at work. Depending on which
server I have logged on to I get an error message on some of my forms when
I
click on a button to run a report, or query. The error message starts "
the
expression you have entered is too complicated or...". Why would it work
on
some servers and not on others? I used the wizard to run the button, so
it
isn't an error in my coding. I thought I had seen the answer here a while
ago but can't find it. Could anyone help me with a solution or an
explanation.
Thanks
