[Nathan Franklin]

Hello Guys,

I am struggling with this. I am trying to mail merge using a query from ms
access. I am linking tables from another database on the server and then
setting the datsource mailmerge to this datbase.

The problem when i use the replace function in ms access in my sql query it
causes an error to come up in microsoft word. I have tested this over and
over and can reproduce or get rid the problem every time through use or no
use of the replace function

The error message is
"This operation cannot be completed because of dialog or database engine
failures.Please try again later."

My sql statement is
SELECT FirstName & ' ' & LastName AS Purchaser,
replace(PropertyAddress,Chr(13),'') AS Property_address, '#' &
right('000000000' & InvoiceID,5) AS Invoice, ReportFirstName & ' ' &
ReportLastName AS Requested_by, Address AS Address_1, PhoneNumber & ' ' &
MobileNumber AS Phone, DatePart('d',DateInspected) & '/' &
DatePart('m',DateInspected) & '/' & DatePart('yyyy',DateInspected) AS
Date_of_inspection, FaxNumber AS Fax, '' AS Vendor, '' AS Age, (Select
DatePart('d',DateCreated) & '/' & DatePart('m',DateCreated) & '/' &
DatePart('yyyy',DateCreated) From invoices Where ID = inspections.InvoiceID)
AS Invoice_date
FROM inspections;

special attention on replace(PropertyAddress,Chr(13),'') AS Property_address

I have even tried '' & replace(PropertyAddress,Chr(13),'') AS
Property_address to force a text field, but word still spits the dummy.

Could someone please help me solve this. Thanks!

[Peter Jamieson]

I think you're probably using OLEDB or ODBC to connect to your Access
database, and unfortunately when you do that, there are some functions that
you cannot use. Mostly they are the complex "series" financial functions,
but "replace" is also on the list - it isn't obvious why, but I saw an
explanation ages ago that sugests that for some reason it hasn't been added
to some list that's probably used by the Jet database engine or the OLEDB
provider.

I think you will have to do one of the following things to fix this:
a. create a view (or have someone create a view) in your server dataabse
that does the replace, then link to that view (probably the best way to go
if you can)
b. use DDE to get your data instead of OLEDB/ODBC. Unfortunately Word then
has to start Access, open the database, and so on, but to try it, check Word
Tools|Options|General|"Confirm conversion at Open", go through the process
of connecting to your data source again, and select the DDE option when it
is offered.

Peter Jamieson

"Nathan Franklin" wrote in message
...
Hello Guys,

I am struggling with this. I am trying to mail merge using a query from ms
access. I am linking tables from another database on the server and then
setting the datsource mailmerge to this datbase.

The problem when i use the replace function in ms access in my sql query
it causes an error to come up in microsoft word. I have tested this over
and over and can reproduce or get rid the problem every time through use
or no use of the replace function

The error message is
"This operation cannot be completed because of dialog or database engine
failures.Please try again later."

My sql statement is
SELECT FirstName & ' ' & LastName AS Purchaser,
replace(PropertyAddress,Chr(13),'') AS Property_address, '#' &
right('000000000' & InvoiceID,5) AS Invoice, ReportFirstName & ' ' &
ReportLastName AS Requested_by, Address AS Address_1, PhoneNumber & ' ' &
MobileNumber AS Phone, DatePart('d',DateInspected) & '/' &
DatePart('m',DateInspected) & '/' & DatePart('yyyy',DateInspected) AS
Date_of_inspection, FaxNumber AS Fax, '' AS Vendor, '' AS Age, (Select
DatePart('d',DateCreated) & '/' & DatePart('m',DateCreated) & '/' &
DatePart('yyyy',DateCreated) From invoices Where ID =
inspections.InvoiceID) AS Invoice_date
FROM inspections;

special attention on replace(PropertyAddress,Chr(13),'') AS
Property_address

I have even tried '' & replace(PropertyAddress,Chr(13),'') AS
Property_address to force a text field, but word still spits the dummy.

Could someone please help me solve this. Thanks!