Hi,

My name is Feliks, I am from Poland and this is my first post to the
group.
I have the following problem related to mailmerge, QueryString command
and SQL sentence in Word 2003.
In my code I need to perform the following SQL operation with
ActiveDocument.MailMerge.DataSource.QueryString command:

SELECT * FROM x WHERE (field_1 = A OR field _1 = B OR field _1 = C) AND
field _2 = D AND field _3 = E and field_3 =F ORDER BY field_4 ASC

The command is executed without any problems however records retrieved
by the command do not match the above criteria. Instead I get records
which match the following:

SELECT * FROM x WHERE field _1 = A OR field_1 = B OR field _1 = C AND
field _2 = D AND field _3 = E ORDER BY field _4 ASC

In other words parentheses are ignored in this logic sentence and get
completely different set of records as it is supposed to be.

I tried different combinations a) with "(field_1 =A OR ... )" part
at the end (before ORDER), b) with additional parenthesis embracing the
part between WHERE and ORDER etc, the result is always not as expected.

Is there any way to force Word to accept the "parenthesis logic"?
Has anyone a clue how to do it?

I am enclosing the part of my real code which is a subject of my
question (and frustration):

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

Any comment will be highly appreciated.

Regards,
FP

Try:

SELECT *
FROM x
WHERE (((Field_1)="A") AND ((Field_2)="D") AND ((Field_3)="E") AND
((Field_3)="F")) OR (((Field_1)="B") AND ((Field_2)="D") AND
((Field_3)="E") AND ((Field_3)="F")) OR (((Field_1)="C") AND
((Field_2)="D") AND ((Field_3)="E") AND ((Field_3)="F")) ORDER BY field _4
ASC


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

My name is Feliks, I am from Poland and this is my first post to the
group.
I have the following problem related to mailmerge, QueryString command
and SQL sentence in Word 2003.
In my code I need to perform the following SQL operation with
ActiveDocument.MailMerge.DataSource.QueryString command:

SELECT * FROM x WHERE (field_1 = A OR field _1 = B OR field _1 = C) AND
field _2 = D AND field _3 = E and field_3 =F ORDER BY field_4 ASC

The command is executed without any problems however records retrieved
by the command do not match the above criteria. Instead I get records
which match the following:

SELECT * FROM x WHERE field _1 = A OR field_1 = B OR field _1 = C AND
field _2 = D AND field _3 = E ORDER BY field _4 ASC

In other words parentheses are ignored in this logic sentence and get
completely different set of records as it is supposed to be.

I tried different combinations a) with "(field_1 =A OR ... )" part
at the end (before ORDER), b) with additional parenthesis embracing the
part between WHERE and ORDER etc, the result is always not as expected.

Is there any way to force Word to accept the "parenthesis logic"?
Has anyone a clue how to do it?

I am enclosing the part of my real code which is a subject of my
question (and frustration):

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

Any comment will be highly appreciated.

Regards,
FP

Try what Doug suggested...then...

What is the data source? if it is a Word document or another data source
where Word is using its internal dialect of SQL, the dialect of SQL used is
much more limited than normal SQL. In that case, if the query cannot be
trqnsformed into the 5-row box in the (Advanced) Query Options dialog box,
Word may not process it properly - in other words, with those types of data
source you may not be able to issue the query you want.

Peter Jamieson

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

My name is Feliks, I am from Poland and this is my first post to the
group.
I have the following problem related to mailmerge, QueryString command
and SQL sentence in Word 2003.
In my code I need to perform the following SQL operation with
ActiveDocument.MailMerge.DataSource.QueryString command:

SELECT * FROM x WHERE (field_1 = A OR field _1 = B OR field _1 = C) AND
field _2 = D AND field _3 = E and field_3 =F ORDER BY field_4 ASC

The command is executed without any problems however records retrieved
by the command do not match the above criteria. Instead I get records
which match the following:

SELECT * FROM x WHERE field _1 = A OR field_1 = B OR field _1 = C AND
field _2 = D AND field _3 = E ORDER BY field _4 ASC

In other words parentheses are ignored in this logic sentence and get
completely different set of records as it is supposed to be.

I tried different combinations a) with "(field_1 =A OR ... )" part
at the end (before ORDER), b) with additional parenthesis embracing the
part between WHERE and ORDER etc, the result is always not as expected.

Is there any way to force Word to accept the "parenthesis logic"?
Has anyone a clue how to do it?

I am enclosing the part of my real code which is a subject of my
question (and frustration):

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

Any comment will be highly appreciated.

Regards,
FP

Hi again,

Thank you for your prompt reply.

I tried to do this logical distribution as Doug suggested ( I retried
now, to be sure) but it ended up now and then with Command Failed (r/t
error "4198") message. I checked syntax many times and it is correct. I
think the reason may be in what Peter points to .

The data source is MS Access query. Actually, I suspected that it may
have something to do with transformation to Query Options box since
after execution of my original sentence I could see that Query Options
box was filled in with fields from my sentence as well as OR and AND
operators are were set accordingly but as there is no way to put
parentheses into the box they were ignored I guess.
I hoped there is a sort of workaround.

For sake of good order let me mentioned that there was a little mistake
in my previous post and the part which start after words "Instead I get
records
which match the following: " should be read:

SELECT * FROM x WHERE field_1 = A OR field _1 = B OR field _1 = C AND
field_2 = D AND field_3 = E AND field_3 =F ORDER BY field_4 ASC

Best regards,
FP


Peter Jamieson napisal(a):
Try what Doug suggested...then...

What is the data source? if it is a Word document or another data source
where Word is using its internal dialect of SQL, the dialect of SQL used is
much more limited than normal SQL. In that case, if the query cannot be
trqnsformed into the 5-row box in the (Advanced) Query Options dialog box,
Word may not process it properly - in other words, with those types of data
source you may not be able to issue the query you want.

Peter Jamieson

I think the reason may be in what Peter points to .

No, if you are using Access then the limit on complexity that I described
would not apply. If you do not use the Query Options box after you have set
QueryString, Word should not change the query.

When I re-read your query text, the main thing that I notice is that you
have

AND field _3 = E and field_3 =F

If you actually mean field_3 = 'E' AND field_3 = 'F', it's never going to
work because the two conditions are mutually exclusive. I wonder if you mean
field_4 = 'F' ?

Peter Jamieson

"FP" wrote in message
oups.com...
Hi again,

Thank you for your prompt reply.

I tried to do this logical distribution as Doug suggested ( I retried
now, to be sure) but it ended up now and then with Command Failed (r/t
error "4198") message. I checked syntax many times and it is correct. I
think the reason may be in what Peter points to .

The data source is MS Access query. Actually, I suspected that it may
have something to do with transformation to Query Options box since
after execution of my original sentence I could see that Query Options
box was filled in with fields from my sentence as well as OR and AND
operators are were set accordingly but as there is no way to put
parentheses into the box they were ignored I guess.
I hoped there is a sort of workaround.

For sake of good order let me mentioned that there was a little mistake
in my previous post and the part which start after words "Instead I get
records
which match the following: " should be read:

SELECT * FROM x WHERE field_1 = A OR field _1 = B OR field _1 = C AND
field_2 = D AND field_3 = E AND field_3 =F ORDER BY field_4 ASC

Best regards,
FP


Peter Jamieson napisal(a):
Try what Doug suggested...then...

What is the data source? if it is a Word document or another data source
where Word is using its internal dialect of SQL, the dialect of SQL used
is
much more limited than normal SQL. In that case, if the query cannot be
trqnsformed into the 5-row box in the (Advanced) Query Options dialog
box,
Word may not process it properly - in other words, with those types of
data
source you may not be able to issue the query you want.

Peter Jamieson

No, if you are using Access then the limit on complexity that I described
would not apply. If you do not use the Query Options box after you have set
QueryString, Word should not change the query.


So still I am wondering what causes the extra amount of retrieved
records. As mentioned before, I checked the Query Option box after
running the QueryString command (and getting too high amount of
records) and the box was filled in with the fields and logical
operators as like there were no parentheses in my SQL sentence.

AND field _3 = E and field_3 =F

If you actually mean field_3 = 'E' AND field_3 = 'F', it's never going to
work because the two conditions are mutually exclusive. I wonder if you mean
field_4 = 'F' ?

This part in the real sentence is as follows:

.... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" & st_d
& "#) ...

Field_3 is `SignOnDate` and the conditions are not mutally exclusive if
"en_d" (end date) is greater or equal to "st_d" (start_date).
For example, take 2006-06-09 as "en_d" and 2006-06-03 as "st_d" and you
get" SignOnDate =2006-06-03 and SignOnDate = 2006-06-09 (I guess
order of conditions is meaningless in case of AND function).
Anyway, if I run this sentence with only one option of Field_1, i.e.
only AND operators in the sentence, it is executed properly. Problem
starts when I add an OR operator.

Best regards,
FP

OK, now we know you are working with dates, there is another problem, which
is that although the #2006-01-01# syntax for date literals works in an
Access query, it doesn't appear to work when you issue the same code from
VBA. I do not know which piece of software causes this problem, but I think
it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

.... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" & st_d
& "#) ...

to

.... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate` =
datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work reliably,
you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver and it's
probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide the
complete code of your SQL statement and the kind of values A,B,C,D etc. can
be.

Peter Jamieson

"FP" wrote in message
ups.com...
No, if you are using Access then the limit on complexity that I described
would not apply. If you do not use the Query Options box after you have
set
QueryString, Word should not change the query.


So still I am wondering what causes the extra amount of retrieved
records. As mentioned before, I checked the Query Option box after
running the QueryString command (and getting too high amount of
records) and the box was filled in with the fields and logical
operators as like there were no parentheses in my SQL sentence.

AND field _3 = E and field_3 =F

If you actually mean field_3 = 'E' AND field_3 = 'F', it's never going
to
work because the two conditions are mutually exclusive. I wonder if you
mean
field_4 = 'F' ?

This part in the real sentence is as follows:

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" & st_d
& "#) ...

Field_3 is `SignOnDate` and the conditions are not mutally exclusive if
"en_d" (end date) is greater or equal to "st_d" (start_date).
For example, take 2006-06-09 as "en_d" and 2006-06-03 as "st_d" and you
get" SignOnDate =2006-06-03 and SignOnDate = 2006-06-09 (I guess
order of conditions is meaningless in case of AND function).
Anyway, if I run this sentence with only one option of Field_1, i.e.
only AND operators in the sentence, it is executed properly. Problem
starts when I add an OR operator.

Best regards,
FP

Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
.... And (`SignOnDate` = '" & DateValue(en_d) & "') And (`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

.... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" & vsl
& "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another problem, which
is that although the #2006-01-01# syntax for date literals works in an
Access query, it doesn't appear to work when you issue the same code from
VBA. I do not know which piece of software causes this problem, but I think
it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" & st_d
& "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate` =
datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work reliably,
you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver and it's
probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide the
complete code of your SQL statement and the kind of values A,B,C,D etc. can
be.

Peter Jamieson

You have a horrible mix of smart quotes and non-smart quotes in that lot
that you might attend to first?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


FP wrote:
Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
... And (`SignOnDate` = '" & DateValue(en_d) & "') And (`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" &
vsl & "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` =
#" & st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another
problem, which is that although the #2006-01-01# syntax for date
literals works in an Access query, it doesn't appear to work when
you issue the same code from VBA. I do not know which piece of
software causes this problem, but I think it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate` = datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work
reliably, you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver and
it's probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide
the complete code of your SQL statement and the kind of values
A,B,C,D etc. can be.

Peter Jamieson

Hi Graham,

In this case, the ` aren't usually smart quotes - theyare back quotes that
are (sometimes) needed to quote names in the SQL statement. Typically you
can surround the name by [ ] instead but it may depend on the dialect of SQL
etc.

Peter Jamieson

"Graham Mayor" wrote in message
...
You have a horrible mix of smart quotes and non-smart quotes in that lot
that you might attend to first?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


FP wrote:
Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
... And (`SignOnDate` = '" & DateValue(en_d) & "') And (`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" &
vsl & "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` =
#" & st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another
problem, which is that although the #2006-01-01# syntax for date
literals works in an Access query, it doesn't appear to work when
you issue the same code from VBA. I do not know which piece of
software causes this problem, but I think it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate` = datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work
reliably, you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver and
it's probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide
the complete code of your SQL statement and the kind of values
A,B,C,D etc. can be.

Peter Jamieson

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

"FP" wrote in message
ups.com...
Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#) And
(`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
... And (`SignOnDate` = '" & DateValue(en_d) & "') And (`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" & vsl
& "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another problem,
which
is that although the #2006-01-01# syntax for date literals works in an
Access query, it doesn't appear to work when you issue the same code from
VBA. I do not know which piece of software causes this problem, but I
think
it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" & st_d
& "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And (`SignOnDate` =
datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work reliably,
you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver and it's
probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide the
complete code of your SQL statement and the kind of values A,B,C,D etc.
can
be.

Peter Jamieson

Oops!

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Peter Jamieson wrote:
Hi Graham,

In this case, the ` aren't usually smart quotes - theyare back quotes
that are (sometimes) needed to quote names in the SQL statement.
Typically you can surround the name by [ ] instead but it may depend
on the dialect of SQL etc.

Peter Jamieson

"Graham Mayor" wrote in message
...
You have a horrible mix of smart quotes and non-smart quotes in that
lot that you might attend to first?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


FP wrote:
Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#)
And (`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
... And (`SignOnDate` = '" & DateValue(en_d) & "') And
(`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" &
vsl & "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` =
#" & st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another
problem, which is that although the #2006-01-01# syntax for date
literals works in an Access query, it doesn't appear to work when
you issue the same code from VBA. I do not know which piece of
software causes this problem, but I think it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate` = datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work
reliably, you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver
and it's probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide
the complete code of your SQL statement and the kind of values
A,B,C,D etc. can be.

Peter Jamieson

It is quite confusing, particularly since there's also a special use of { }
in ODBC SQL strings as well, just to throw a spanner in the works for people
who've just got used to using ctrl-F9 to insert 'em :-). Syntactically the
`` or [] should only be needed to surround names with spaces and other
unhelpful characters (and possibly for use in conjunction with DDE and
Access Form references etc.) but some of the providers seem to insist you
have syntactically unnecessary stuff anyway.

Peter Jamieson

"Graham Mayor" wrote in message
...
Oops!

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Peter Jamieson wrote:
Hi Graham,

In this case, the ` aren't usually smart quotes - theyare back quotes
that are (sometimes) needed to quote names in the SQL statement.
Typically you can surround the name by [ ] instead but it may depend
on the dialect of SQL etc.

Peter Jamieson

"Graham Mayor" wrote in message
...
You have a horrible mix of smart quotes and non-smart quotes in that
lot that you might attend to first?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


FP wrote:
Hi,

The whole code responsible for the sentence is as follows:

Dim vsl, qs, airport_query , st_d, en_d As String
Dim start_dt, end_dt As Date

airport_query = " (`Airport` = 'Berlin' OR `Airport` = 'Szczecin' OR
`Airport` = 'Szczecin/Berlin') AND "
st_d = Year(start_dt) & "-" & Month(start_dt) & "-" & Day(start_dt)
en_d = Year(end_dt) & "-" & Month(end_dt) & "-" & Day(end_dt)
vsl = Me.tbVessel.Text

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE" & airport_query &
"(`VslName` = '" & vsl & "') And (`SignOnDate` = #" & en_d & "#)
And (`SignOnDate` = #" & st_d & "#) ORDER BY `City` ASC"
ActiveDocument.MailMerge.DataSource.QueryString = qs & ""

As you can see st_d and en_d are strings formatted as dates in SQL
syntax.
I tried to add "datevalue" command as you suggested but it has not
changed anything. BTW, the syntax of the part should be:
... And (`SignOnDate` = '" & DateValue(en_d) & "') And
(`SignOnDate`
= '" & DateValue(st_d) & "') ...

because if "datevalue" is placed within SQL sentence, i.e.

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate`
= datevalue('" & st_d & "')) ...

I get Command Failed error.

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

airport_query = " " (in the code, there is a IF condition which sets
the string based on user's input),

so the sentence looks as follows:

qs = "SELECT * FROM `non-NISSeafarersQuery` WHERE (`VslName` = '" &
vsl & "') And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` =
#" & st_d & "#) ORDER BY `City` ASC"

the records are retrieved properly.

Best regards,
FP


Peter Jamieson napisal(a):
OK, now we know you are working with dates, there is another
problem, which is that although the #2006-01-01# syntax for date
literals works in an Access query, it doesn't appear to work when
you issue the same code from VBA. I do not know which piece of
software causes this problem, but I think it is the OLEDB provider.

There may be a better workaround, but you can try the following:

Change

... And (`SignOnDate` = #" & en_d & "#) And (`SignOnDate` = #" &
st_d & "#) ...

to

... And (`SignOnDate` = datevalue('" & en_d & "')) And
(`SignOnDate` = datevalue('" & st_d & "')) ...

(and the same for any other date comparisons). For this to work
reliably, you probably need to use YYYY-MM-DD format for the date.

There is another approach, but it involves using the ODBC driver
and it's probably best to keep to the OLEDB provider if you can.

If that still hasn't identified the problem, can you please provide
the complete code of your SQL statement and the kind of values
A,B,C,D etc. can be.

Peter Jamieson

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

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