Re: Mailmerge, querystring and (un)logical SQL (Word 2003)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: Using SQL in Sub to fill box on mouseclick
    ... In addition to Doug's point on the SQL syntax, ... This is because the first " character in your SQL string will be seen by VBA ...
    (microsoft.public.access.formscoding)
  • Re: Set up Parameter Query to accept more than one entry
    ... I don't know what your region field name is but I think you might have ... included two "WHERE"s in your SQL which isn't allowed. ... The expression you entered contains invalid> syntax. ... > If I try to paste the string you recommended directly into SQL View when I> save it, it gives an error message that says: ...
    (microsoft.public.access.queries)
  • Using a variable in SQL
    ... I'm trying to replace an integer number in a test WHERE string of my SQL with a variable. ... See below code snippet. ... Any syntax suggestions appreciated. ...
    (microsoft.public.vb.database)
  • SQL Syntax for a single quote in a string.
    ... I don't know how to write a syntax in SQL if there is a quote mark in a string. ...
    (borland.public.delphi.database.ado)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)