glad it worked in the end and thanks for the feedback.

Peter Jamieson

"FP" wrote in message
oups.com...
Peter,

You made my day!

I have retried on datavalue within SQL sentence and it worked out!
Probably I made a syntax error or the sentence was too long (I thought
it was 512 chars limit and did not bother, anyway error 4198 should
have been described properly in help but it is not).
With "datevalue" inside SQL I can run queries with IN or LIKE
operators, which was not possible before (I tried before unsuccessfully
the following:
airport_query = " (`Airport` LIKE ('%Berlin%') AND "
which would do as well).

Indeed if a shorten the sentence under 255 chars (e.g. removing ORDER
BY part) I can use OR operator as well! (however I do not need it now
since LIKE operator works)

Thank you very much for you help!

For the record:
Before retrying datavalue thing as described above I followed you
suggestion and temporarily changed the data source to MS Access table
(updating the data source in the SQL sentence, of course), but the
results were the same:
"Command Failed" when running the query with IN or LIKE operators in
"airport_query" part or
Too many records retrieved if executing the query with OR operators.

Best Regards,
FP


Peter Jamieson napisal(a):
Honestly, I do not think the date value format is a clue to this
problem. If I run this query with:

You could be right. If I also reduce the airport_query to " " I also get
the
correct results using the # # format.

However, all I can say is that the date value format makes a great deal
of
difference here when the full airport_query is used.

An approach that appears to work is to use the following code for
airport_query:

airport_query = " (`Airport` IN ('Berlin','Szczecin','Szczecin/Berlin'))
AND
"

Even if that works, you may find that performance is affected if you have
a
lot of data.

However, the previous code I suggested using datevalue does work, and is
the
correct code (in other words, the string "datevalue('whatever')" needs to
be
in the SQL query), except for two very unhelpful (known) problems with
Word:
a. inserting the "datevalue" texts increases the SQL string beyond a
25/256
character limit, which is why you receive an error.
b. normally you can exceed this limit by using SQLStatement and
SQLStatement1 in MailMerge.OpenDataSource, allowing you a SQL string of
up
to around 511 characters, but there is a further error in Word where for
some OLEDB data sources, the total length of SQLStatement+SQLStatement1
is
also limited to around 255 characters.

The only way around this is to try to reduce the length of the query
code,
perhaps by
a. including more of the query code in queries stored in Access
b. using shorter query names or column names in those queries
c. eliminating unneeded braces
d. (perhaps) using aliases

The other main factor that may affect my analysis so far is that,
assuming
"non-NISSeafarersQuery" is an Access query and not an Access table, the
combination of the query code in the query and the query code in Word may
also affect the result. (Yes, I know that the query /should be/ like a
"black box" and that you should be able to treat it as if it is a table,
but
in my experience "black boxes" are rarely as black as they are supposed
to
be.

Peter Jamieson