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