This error indicates that Access cannot interpret the query statement
correctly. This can happen if the data types are misunderstood, if the
delimiters are not correct, if the field/table names contain characters such
as spaces but are not in square brackets, if the bracketing is ambiguous, if
the field/table names use reserved words, or if the SQL statement is
incorrect/unclear.
Since it works on one computer and not others, you might start by checking
if there is a difference in regional settings. For example, if the list
delimiter is comma on one machine and semicolon on the other, or if the date
formats are different.
Does the query contains any calculated fields? If so, try typecasing them to
ensure Access understands the data type. More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
Does the query contain any parameters? If so, explicitly declare them by
choosing Parameters on the Query menu, and specifying the data type for each
one.
Does the query refer to controls on a form? If so, set the Format property
of the text boxes, especially if they are dates. More info in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
Then be sure to declare these as parameters also. The parameters dialog
typically ends up with a couple of rows, like this:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time
Look at the Criteria under any calculated field. If you intend the field to
be matched as Text (not Number), make sure any literal values are in quote
marks. If you intend the field to be matched as as date, make sure literals
are delimited with #. If the field is to be matched as a number, there
should be no delimiters around the literal number in the Criteria row.
Look at the SQL statement directly. Access tends to put in lots of brackets
where they are not needed. You may be able to simplify it down, and ensure
the brackets are unambiguous.
You can also get error 3071 if there are too many ANDs/ORs in the WHERE
clause. However, you should not see this working on one machine and failing
on another unless they are using different versions of Access.
If none of this solves the problem, then it is possible that Name
AutoCorrupt has confused Access about what the names in the query refer to.
If this is the problem, you could solve it by unchecking the boxes under:
Tools | Options | General | Name AutoCorrect
and then compacting the database.
More info on these corruptions:
http://allenbrowne.com/bug-03.html
HTH
