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