Access Query returns no records in MailMerge

I have a simple select query in Access XP that returns the records I need for a MailMerge. However, when I connect to it using the default OLE connection in Word XP, the same query returns zero records. Use DDE seems to work fine. Here is the Select statement.

SELECT MemberID, FirstName, LastName, Address1, City, State, Zip, MemExpire, CancelRequested FROM Members WHERE (Email Like "*@*.*") AND (MemExpire#3/1/2004#) And (MemExpire#7/1/2004#) AND (CancelRequested=False);

What is causing the problem? Is it the Like condition? Any guidance would be appreciated.

This kind of connection seems to require more bits of SQL syntax than are
strictly necessary. e.g.
a. you need to quote your table name, either using `` or [] - `Members`.
Otherwise the query fails
b. you need to qualify your column names in the column list with the table
name - Members.MemberID and so on. Otherwise, it is as if you requested
SELECT *. However, you do not seem to have to qualify the field names in the
WHERE clause.

In addition,
c. when you use OLEDB you have to use the wildcard characters % and _
instead of * and ?, e.g.

(Email Like %@%.%)

d. your dates #3/1/2004# etc. may be interpreted as being in MM/DD/YYYY
format even if your regional settings are set to DD/MM/YYYY format. (This is
probably irrelevant in e.g. the USA but would typically cause problems here
in the UK, where you might be better off using #YYYY-MM-DD# format, e.g.
#2004-12-31#

Everything else looks OK to me.

--
Peter Jamieson

"Joe Brazell" wrote in message
...
I have a simple select query in Access XP that returns the records I need
for a MailMerge. However, when I connect to it using the default OLE
connection in Word XP, the same query returns zero records. Use DDE seems
to work fine. Here is the Select statement.

SELECT MemberID, FirstName, LastName, Address1, City, State, Zip,
MemExpire, CancelRequested FROM Members WHERE (Email Like "*@*.*") AND
(MemExpire#3/1/2004#) And (MemExpire#7/1/2004#) AND
(CancelRequested=False);

What is causing the problem? Is it the Like condition? Any guidance would
be appreciated.

I cleaned up the query a little from the Access interpretation of the SQL. I am not running the SQL statement directly but am using the built in Word features to retrive the data from the Access database query. Since the query is in Access and is used by several different reports or forms in Access, I cannot use the % as a wildcard. Access does not recognize the % as a wildcard unless you know a way of changing it. My current MDB format is Access 2000. As soon as the Jet Engine recognizes the SQL wild cards then this will not be an issue. I think Microsoft has nearly put the cart before the horse so to speak in setting up Word to use OLE as the default connection method but internally not having a consistency in the JET and OLE engines in the use of wildcards.

thanks
Joe

Sorry, I got the idea you were using VBA.

One thing you can do is define an additional query in Access that uses the %
syntax. It will not return anything in Acess, but it should work from Word
via OLEDB.

My current MDB format is Access 2000. As soon as the Jet Engine
recognizes the
SQL wild cards then this will not be an issue.

Well, the database can certainly be changed to recognise % instead of *
using Access|Tools|Options|Tables/Queries|"SQL Server Compatible Syntax
(ANSI 92)"|"This database", but I do not know Access/Jet well enough to be
sure that that means
/everything/ will use % instead of *. What I do know is that * will no
longer function as before.

--
Peter Jamieson

"Joe Brazell" wrote in message
...
I cleaned up the query a little from the Access interpretation of the SQL.
I am not running the SQL statement directly but am using the built in Word
features to retrive the data from the Access database query. Since the
query is in Access and is used by several different reports or forms in
Access, I cannot use the % as a wildcard. Access does not recognize the %
as a wildcard unless you know a way of changing it. My current MDB format
is Access 2000. As soon as the Jet Engine recognizes the SQL wild cards
then this will not be an issue. I think Microsoft has nearly put the cart
before the horse so to speak in setting up Word to use OLE as the default
connection method but internally not having a consistency in the JET and OLE
engines in the use of wildcards.

thanks
Joe

Thanks for the information. I had not found the SQL Server Compatible Syntax option. For part of what I was doing, I am early enough in the project that I think I can switch quickly to the SQL Server syntax. Still would have been nice if Microsoft had left a way of keeping the Access syntax available for at least one version while the SQL syntax was activated. Transitions are always fun. ;-)

Thanks for your assistance.