Word recipient list empty with Access query.

[wackywoo105]

I have a query in Access and a word document that opens up the query. Currently the query contains every record in the database. If I alter the query in Access to select a certain set of records (based on dates) when Word opens the recipient list is empty. Checking for errors it says there were no records or no data records matched query options.



How can I get Word to open and use the modified query?

[June7]

The query works correctly in Access? Word is linking to the query? Is this mail merge?

[wackywoo105]

I should have been quicker to search for an answer. It seem replacing "*" for "%" in the query solves the issue.

Mods please delete if you feel the need.

[June7]

Thanks for sharing the solution. We will leave, might help someone else.

[wackywoo105]

In that case just to add a bit more info:

I build the query with SQL in VBA. Using wilcards "*" works fine in access but then word reports no data. Using "%" instead of "*" shows no records in access but when word loads the query all the records are there.

I have just created 2 versions of the query. One I can open in access and the other is the one word links to. Both work fine in their respective programs.

E.g. ok with access but word shows no records

SELECT Patients.Title FROM Patients WHERE Patients.[Due Date] Like "*/06/*" ORDER BY Patients.[Due Date];

And this is ok with word but access shows no records

SELECT Patients.Title FROM Patients WHERE Patients.[Due Date] Like "%/06/%" ORDER BY Patients.[Due Date];

[June7]

Is that day 06?

Is [Date Due] a date/time data type?

An alternative could be:

SELECT Title FROM Patients WHERE DatePart("d",[Due Date]) = 6 ORDER BY [Due Date];

I do wonder why not limiting records to current month and/or year.

[wackywoo105]

The code is just a trimmed down snippet to show what the problem was, the actual SQL statement is very long and selects multiple dates (and has lots of chr(34) and variables in it as it's in VBA). I don't know where The Great Land is but where I am that selects all days and years for month 6.

I'm a bit of a novice at this an don't write optimised code but it does the job bar the odd hiccup.

[June7]

Viewing the date as mm/dd/yyyy structure and considering the wildcards of the date parameter, it looks like you want all months and years for day 6 - which is why I used "d" in the DatePart function.

Just wanted to show that should probably be able to structure the SQL so don't need two versions.

Why the need for Chr(34)? I use apostrophe to delimit text parameters.