Re: MailMerge Query Discontinuity

From: Ken (Ken_at_discussions.microsoft.com)
Date: 07/25/04 

Date: Sat, 24 Jul 2004 19:43:03 -0700

Peter, many thanks for following up on my post.

The data source is an ODBC connection string with (1) a DSN that references an MS SQL database and (2) an SQLStatement that EXECs one of the db's stored procedures.

The three problem columns are references to a type nvarchar(50) in the database table CorStatDataOP.Doctor. This is a lookup table inner joined in the stored procedure's Select.

The use of the Null value in our db's columns is not at issue here. The nature of our data capture software guarantees that the values of these columns in our db will not be Null.

The problem is that the query's result rowset has No Null (missing, blank, etc.) values for these three columns, but the MailMerge Recipents list does.

Put another way, when I run the query with QA or Access or MS Query there are no Null (missing, blank) values. When Word runs the query, three columns that ref the same lookup table contain Null (blank, missing) values.

This MailMerge query had been wotking fine. The only change I am aware of is the Doctor control table had a few rows added, growing in size from 60 to 68 rows.

I'm bewildered!

Ken

P.s., this is probably more detail than you want, but the SP follows.

CREATE PROCEDURE uspWordQuery @chrPATID char(9), @chrVisitDate char(18)

as

SELECT Patient.PATID,
        Patient.TITLE,
        Patient.LNAME,
        Patient.FNAME,
        Patient.MINIT,
        Patient.SSN,
        Convert(VarChar,Visit.VDate,107) AS 'VDate',
        Visit.MRNOP,
        Visit.Cardiol,
        Visit.ReferMD,
        Visit.Office,
        Nurse.Name AS 'EPRNName',
        Doctor.Name AS 'EPMDName',
        Doctor2.Name AS 'CardiolName',
        Doctor3.Name AS 'ImpCarName',
        EPDevice.FullName,
        EPDevice.Description,
        EPDevice2.FullName AS 'ALeadName',
        EPDevice3.FullName AS 'VLeadName',
        Office.State,
        Office.Zip,
        Office.Phone,
        Office.Fax,
        Office.Name AS 'OfficeName',
        Office.Practice,
        Office.Addr,
        Office.City,
        Pacemaker.*,
        PatientTitle.LABEL AS 'TitleText',
        PacemakerMode.Label AS 'ModeText',
        PacemakerDep.Label AS 'PaceDepText',
        PacemakerApolar.Label AS 'PaceAPolText',
        PacemakerVpolar.Label AS 'PaceVPolText',
        PacemakerRAAVDelay.Label AS 'PaceRAAVDelText',
        PacemakerATAFTh.Label AS 'PaceATAFThlText',
        PacemakerModeSw.Label AS 'PaceModeSwText'

FROM CorStatDataOP.dbo.Doctor Doctor,
        CorStatDataOP.dbo.Doctor Doctor2,
        CorStatDataOP.dbo.Doctor Doctor3,
         CorStatDataOP.dbo.EPDevice EPDevice,
         CorStatDataOP.dbo.EPDevice EPDevice2,
         CorStatDataOP.dbo.EPDevice EPDevice3,
        CorStatDataOP.dbo.Nurse Nurse,
        CorStatDataOP.dbo.Office Office,
         CorStatDataOP.dbo.Pacemaker Pacemaker,
        CorStatDataOP.dbo.Patient Patient,
         CorStatDataOP.dbo.Visit Visit,
        CorStatMetadataOP.dbo.PacemakerMode PacemakerMode,
        CorStatMetadataOP.dbo.PacemakerDep PacemakerDep,
        CorStatMetadataOP.dbo.PacemakerApolar PacemakerApolar,
        CorStatMetadataOP.dbo.PacemakerVpolar PacemakerVpolar,
        CorStatMetadataOP.dbo.PacemakerRAAVDelay PacemakerRAAVDelay,
        CorStatMetadataOP.dbo.PacemakerATAFTh PacemakerATAFTh,
        CorStatMetadataOP.dbo.PacemakerModeSw PacemakerModeSw,
        CorStatMetadataOP.dbo.PatientTitle PatientTitle

WHERE Visit.PATID = Pacemaker.PATID AND
        Visit.VDate = Pacemaker.VDate AND
        Visit.PATID = Patient.PATID AND
        Visit.Cardiol = Doctor2.DocID AND
        Visit.Office = Office.OfficeID AND
        Pacemaker.EPRN = Nurse.NurseID AND
        Pacemaker.GenNum = EPDevice.DeviceID AND
        Pacemaker.ALeadNum = EPDevice2.DeviceID AND
        Pacemaker.VLeadNum = EPDevice3.DeviceID AND
        Pacemaker.EPMD = Doctor.DocID AND
        Pacemaker.ImpMD = Doctor3.DocID AND
        Patient.TITLE = PatientTitle.Value_Code AND
        Pacemaker.Mode = PacemakerMode.Value_Code AND
        Pacemaker.PaceDep = PacemakerDep.Value_Code AND
         Pacemaker.Apolar = PacemakerApolar.Value_Code AND
        Pacemaker.Vpolar = PacemakerVpolar.Value_Code AND
         Pacemaker.RAAVDel = PacemakerRAAVDelay.Value_Code AND
        Pacemaker.ATAFTh = PacemakerATAFTh.Value_Code AND
        Pacemaker.ModeSw = PacemakerModeSw.Value_Code AND
        ((Visit.PATID=@chrPATID) AND
        (Visit.VDate=convert(datetime,convert(char 10),@chrVisitDate,121),101)))

The problem columns are Doctor, Doctor2, and Doctor3.

"Peter Jamieson" wrote:

> What is the data source, and what type of data (text, integer) is causing
> the problem?
>
> Generally speaking my experience is that many applications either convert
> nulls to blanks/zeroes etc., depending on the data type, and/or confuse
> nulls, zeros and blanks. Neither Word nor MS Query seems to be an exception,
> e.g. I just set up a SQL Server table with some null values and displayed
> the data in MS Query. Whereas SQL Server can, for a text column, distinguish
> between null and blank, MS Query shows no sign of recognising any values
> (text or numeric) as null, and no way of entering nulls either.
>
> What end result are you trying to achieve? DO you need nulls to appear as
> blanks? Should numeric nulls appear as 0 or blank? Are you trying to filter
> on the basis of null values?
>
> --
> Peter Jamieson
>
> "Ken" <Ken@discussions.microsoft.com> wrote in message
> news:369D9B44-9943-4914-B05F-1DF1F684B5C4@microsoft.com...
> > A MailMerge query runs correctly and the resulting rowset can be observed
> in the
> > Word 2003 MailMerge Recipents list. However it contains columns with Null
> values.
> >
> > When the MailMerge Recipents form's Edit control is clicked, the same
> query result is displayed via a DDE link to Micrsoft Query. The rowset
> displayed by MS Query contains NO Null columns. When 'Return Data to
> Micrsoft Word' is selected from Query's File menu, the Recipents list still
> contains the Null columns.
> >
> > How should I approach this problem - I found nothing specific to this
> symptom in the KB. Thanks,
> >
> > Ken
>
>
>

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
Loading