Data of some Records does not appear in CR9

[VBFnewcomer]

There are 2 problems in the Report...

• Data of some Records does not appear
• Data of Office address appear one line below from where Residence address appears i.e. the Tops of Residence & Office are not same during runtime. It is properly set during design time

The

• db is Access 2003.
• CR9

CounselMaster holds the counselIDs and other info.
There is Table called CounselAddress which stores

counselId
addressLine1
addressLine3
addressLine4
addressType
preferred
cityId

There is table called CityMaster which reads the cityID from the cityID found in the CounselAddress

There is yet another Table counselPhones
which stores

CounselID
phone
phoneType
preferred

CounselMaster is linked to counselAddress & counselPhone (INNER JOINS)
The query2 which retrieves the data from these tables is....

SELECT CityMaster.cityName, IIf(CounselMaster!fName<>'',CounselMaster!fName & IIf(CounselMaster!mName<>'',' ' & CounselMaster!mName,'') & IIf(CounselMaster!lName<>'',' ' & CounselMaster!lName,''),'N.A.') AS counselName, CounselAddress.addressLine1, CounselAddress.addressLine2, CounselAddress.addressLine3, CounselAddress.addressLine4, CounselAddress.addressType, CounselAddress.preferred, CounselAddress.PIN, CounselPhones.phone, CounselPhones.phoneType, CounselPhones.preferred
FROM CounselPhones INNER JOIN ((CounselMaster INNER JOIN CounselAddress ON CounselMaster.counselID = CounselAddress.counselID) INNER JOIN CityMaster ON CounselAddress.cityID = CityMaster.cityID) ON CounselPhones.counselID = CounselMaster.counselID
WHERE (((CounselMaster.inPanel)=Yes))
ORDER BY CityMaster.cityName, IIf(CounselMaster!fName<>'',CounselMaster!fName & IIf(CounselMaster!mName<>'',' ' & CounselMaster!mName,'') & IIf(CounselMaster!lName<>'',' ' & CounselMaster!lName,''),'N.A.');

In the CR I have added two address fields as below...

//Formula Field - AddressOffice
if {Query2.addressType}= "OFFICE/WORK" then
    if {Query2.addressLine1}<>"" then 
        {Query2.addressLine1} 
        & if {Query2.addressLine2}<>"" then 
            chr(10)& chr(13) & {Query2.addressLine2} 
            & if {Query2.addressLine3}<>"" then 
                chr(10)& chr(13) & {Query2.addressLine3} 
            else '' 
    else '' 
    else ''
else 
''
;

 //Formula Field - AddressOffice
if {Query2.addressType}= "RESIDENCE" then
    if {Query2.addressLine1}<>"" then 
        {Query2.addressLine1} 
        & if {Query2.addressLine2}<>"" then 
            chr(10)& chr(13) & {Query2.addressLine2} 
            & if {Query2.addressLine3}<>"" then 
                chr(10)& chr(13) & {Query2.addressLine3} 
            else '' 
        else '' 
    else ''
else 
''
;

Grouping (in Report) is done 1st by cityName and then by counselName(which is Hidden) of Query2.
Now when the report is printed, for some records the address comes out (i.e not printed) empty though in the query2 the data is populated. eg under the city Name Bathinda chirangi lal garg & Gopal Chand Gupta no info comes in the report (there could be more such records).

Now for Jai gopal Goyal the office address is printed after residence is printed (i.e) they are not on the same Line). no idea what's happening.

Iam attaching the db & report.

Could somebody let me know what is wrong!!!!!!

Thankz in advance......

[pnish]

The main problem I see is that when a council has BOTH a residential and an office address, 2 records are returned. That's why they're not displayed on the same line.

You should also set the Report Options to Convert Database Null Values to Default. That is causing some values to not be displayed.

I've attached a modified version of your report that works a bit better (though still not perfect). You'll need to reset the Database Location to point to your database.

Hope that helps.

[VBFnewcomer]

Thanks now I see those missing data

You should also set the Report Options to Convert Database Null Values to Default

Its already checked. Additionally I have checked Convert Other Null Values to Default hope that's ok.

But this still puzzles me

• Data of Office address appear one line below from where Residence address appears i.e. the Tops of Residence & Office are not same during runtime. It is properly set during design time

[pnish]

But this still puzzles me

• Data of Office address appear one line below from where Residence address appears i.e. the Tops of Residence & Office are not same during runtime. It is properly set during design time

It's because there's 2 records so each record is displayed on a separate line of the report. Usually you could set the 'Underlay Following Sections' property in the Section Expert and that would fix the problem. That's why I created 2 Detail sections, one for the office address and one for the residence address and then set Underlay Following Sections. For some reason it didn't work though

You may need to modify your query, so that it is all returned in one record.

[VBFnewcomer]

I didn't notice that you have changed that. Though I notice the changes you made in Suppress
by the way what does this Underlay do?

[pnish]

Underlay causes following sections to be placed underneath. For example:

Section1: This is text from section one
Section2: ------------------------------------> This is text from section two

If you set Underlay following sections in Section1, the two sections basically get merged and would (should) print like this:

This is text from section one This is text from section two

Does that make sense?

[VBFnewcomer]

I checked what you were saying in a seperate report. Yes its working there, but in the actual report its not working?
Also the changes you made in the Formulas like adding

formula=""

at the top of the Formula coding, is it necessary..

[pnish]

the changes you made in the Formulas like adding

formula=""

at the top of the Formula coding, is it necessary..

No, probably not. I'ts just a habit of mine.

[VBFnewcomer]

pnish I forgot to ask what was wrong with the code in formula fields I used

[VBFnewcomer]

could somebody improve on pnish example (#2) or show me a better way to solve the problem