Access SQL queries with LIKE condition as Word document data source

[Lhoj]

Hi there,



i use various SQL queries from my database as data source for some Word documents that act as forms. Usually it's simple SQL queries with conditions such as whatever.type=6 or whatever.field is not null. Though I've noticed that when I use a condition such as table.field like '*string*' the results show perfectly on Access, but list appears empty on word when using that query as data source.

Is that a normal behaviour? If so how can I use a query searching for a certain pattern in one field as my data source for Word?

Thank you all so much!

[Gicu]

Not sure what\how this would happen but you can try to use the InStr() function:

SELECT ......FROM tblYourTable WHERE InStr([YourField],"StringToSearchFor")>0

Cheers,

[CJ_London]

but list appears empty on word when using that query as data source.

I presume you are running this query in word? If so, it may be you are using the ADO recordset method which uses % rather than *. To use the dao method, in your vba window ensure the microsoft DAO library is selected and you have dimmed your recordset as DAO.recordset

[Lhoj]

I presume you are running this query in word? If so, it may be you are using the ADO recordset method which uses % rather than *. To use the dao method, in your vba window ensure the microsoft DAO library is selected and you have dimmed your recordset as DAO.recordset

Thank you so much!

I'm using the query stored in the DB as mail merge data source for a Word document. Changing the like wildcard to % does the trick. Then again, where should I add the DAO library? I did so in Word and the * wildcard still fails. Can't add it to the access db cause it gives an error about compatibility issues.

[CJ_London]

Then again, where should I add the DAO library? I did so in Word..

It is now not clear to me where your query is or where it is being called from. Wherever it is, you need to 'type' your recordset as

dim rs as ADO.recordset
or
dim rs as DAO.recordset

[Lhoj]

The query is on the Access DB, has been created through the sql view of the 'Query design' access functionality. I don't see any way to change its type on the object properties or anywhere else. Then that query is used as mail merge data source for a word document (mailings->select recipients->use an existing list, then selecting the DB and the query).

Anyway, since the queries I use for the word documents are used only for that purpose I'll make sure I use % instead of * as a wildcard when searching patterns on fields. That's enough to solve my problem.

[Gicu]

You should also check that your Access client is not set to use SQL Server compatible syntax - ANSI 92 version (under File\Options\Object Designers).

Cheers,

[Lhoj]

You should also check that your Access client is not set to use SQL Server compatible syntax - ANSI 92 version (under File\Options\Object Designers).

Cheers,

Yep, that is unchecked.