Error on report but not on Query?????

[myhnews]

I really can’t figure out why I’m getting an error when running my report.
The report is based on a query, and when running the query everything is fine.

I have attached a sample DB

This DB has only 1 query and one report.

If I remove the fields “Mortgagee” and “Mortgagor” from the report (Not from the query) the report will work fine.

I didn’t have any luck to find the problem.

Can anyone please try your luck???
Thanks
Joe

 

[sfreeman@co.mer]

The paramenter for tblDeed_CorrectionNo: CorrectionNo in your query is 0.

The report doesn't like that.

 

[neileg]

If you look at the results of your query, you'll see that there are a number of problems with the data returned for the fourth record, Deed 17. So although the query has returned some values, some of them are invalid, I think, because the query has not mtached up a record in one of the tables, and is returning nulls and errors.

That's why the report is failing, it expects values and it's getting nulls and errors. There is either a problem with your design or you need to be able to handle the nulls, perhaps with Nz().

 

[myhnews]

Att: Neil,
Yes, you are correct.
There is no record in the sub table.

However, I’m not clear on where I can add your NZ() solution.
Every deed can have multiple mortgages. However, in some cases a deed will not have any mortgages.

I tried to modify the query and the Function NameString but no luck.

Can you please take a look again?

Thanks
Joe

Att: Sfreeman,

The paramenter for tblDeed_CorrectionNo: CorrectionNo in your query is 0.
The report doesn't like that.

Would you have by any chance an other solution to include recodes that has a 0 ???
Thanks
Joe

 

[trucktime]

Query

Joe, may be this link wil help.

http://www.fontstuff.com/access/acctut07.htm

 

[myhnews]

I’m sorry, I can’t find anything related to my problem.

Thanks
Joe

 

[neileg]

Your table design is wrong. You are holding the MortgageRecord ID as a foreign key in both tblMortgagorName and tblMortgageeName. This creates an abiguous outer join when there are no records in these tables. You should be holding the MortgagorID and MortgageeID as foreign keys in tblMortgage.

The way you have it, you will have to create a new record for the Mortgagee and Mortgagor for every Mortgage even if you already have these details.

The error becomes obvious if you add tblMortgagorName and tblMortgageeName to your query grid. As it is, the problem only arises when your custom function StringName() runs and there are no records.

 

[sportsguy]

This will fix the report

but you need to look at your design and really start over from scratch

copy this code into the query SQL, and the report will work

SELECT [tblDeed].[DeedRecordID] AS tblDeed_DeedRecordID, [tblMortgage].[MortgageID], [tblDeed].[DeedID], [tblDeed].[CorrectionNo] AS tblDeed_CorrectionNo, [tblDeed].[CSBLU] AS tblDeed_CSBLU, NameString([tblDeed.DeedRecordID],"tblGranteeName","DeedRecordID") AS Grantee, NameString([tblDeed.DeedRecordID],"tblGrantorName","DeedRecordID") AS Grantor, [tblDeed].[TenancyEE], [tblDeed].[TenancyOR], [tblDeed].[Consideration], [tblDeed].[BookLib] AS tblDeed_BookLib, [tblDeed].[PageX] AS tblDeed_PageX, [tblDeed].[Instrument] AS tblDeed_Instrument, [tblDeed].[DatedDate] AS tblDeed_DatedDate, [tblDeed].[RecordedDate] AS tblDeed_RecordedDate, [tblDeed].[HeldAs], [tblDeed].[DeedAttached], [tblDeed].[DiscriptionAttached], [tblDeed].[NoOfCopies], [tblDeed].[Photo], [tblDeed].[LegalDescription], [tblDeed].[IncludeLegalDescription], [tblDeed].[rBookLib] AS tblDeed_rBookLib, [tblDeed].[rPageX] AS tblDeed_rPageX, [tblDeed].[rInstrument] AS tblDeed_rInstrument, [tblDeed].[NoteX] AS tblDeed_NoteX, [tblMortgage].[MortgageRecordID], [tblMortgage].[CorrectionNo] AS tblMortgage_CorrectionNo, [tblMortgage].[Amount], [tblMortgage].[BookLib] AS tblMortgage_BookLib, [tblMortgage].[PageX] AS tblMortgage_PageX, [tblMortgage].[Instrument] AS tblMortgage_Instrument, [tblMortgage].[DatedDate] AS tblMortgage_DatedDate, [tblMortgage].[RecordedDate] AS tblMortgage_RecordedDate, [tblMortgage].[Satisfied], [tblMortgage].[Assigned], [tblMortgage].[PMM], [tblMortgage].[IncludedInCEMA], [tblMortgage].[Extension], [tblMortgage].[Modification], [tblMortgage].[Subordination], [tblMortgage].[Other], [tblMortgage].[OpenEndedLineOfCredit], [tblMortgage].[MaturityDate], [tblMortgage].[SignedBy], [tblMortgage].[rBookLib] AS tblMortgage_rBookLib, [tblMortgage].[rPageX] AS tblMortgage_rPageX, [tblMortgage].[rInstrument] AS tblMortgage_rInstrument, [tblMortgage].[NoteX] AS tblMortgage_NoteX, [tblMortgage].[DeedOfTrust], IIf(IsNull([tblMortgage.MortgageRecordID]),"",NameString([tblMortgage.MortgageRecordID],"tblMortgageeName","MortgageRecordID")) AS Mortgagee, IIf(IsNull([tblMortgage.MortgageRecordID]),"",NameString([tblMortgage.MortgageRecordID],"tblMortgagorName","MortgageRecordID")) AS Mortgagor
FROM tblDeed LEFT JOIN tblMortgage ON [tblDeed].[DeedRecordID]=[tblMortgage].[DeedRecordID]
WHERE ((([tblDeed].[CorrectionNo])=0)) Or ((([tblMortgage].[CorrectionNo])=0));


sportsguy