Filter mystery - field added to report changes underlying SQL?

[Cindy]

I'm not sure where to begin in posting this, because I'm afraid my
"hunches" might lead the reader down a wrong path (since the path isn't
working for me...) but here goes:

I have a report that uses four tables. The output for the report
varies depending upon whether I add or remove ONE field from the report
canvas.

If I remove the field from the report canvas, I get all the records
that I would expect.

The sql for the report:

SELECT DISTINCTROW Property.PR_STREET, Property.PR_STNO,
Sources.S_TYPE, Sources.S_DETAIL, [Actual Sources].AS_DATE, [Actual
Sources].AS_AMOUNT, [Actual Sources].AS_COMMENT, [Actual
Sources].AS_PRID, [Actual Sources].AS_UTID, Sources.[S_Contract
Number], [Actual Sources].AS_Funding_Request, Property.PR_Name,
nz([Financing],"No financing source entered") AS FundSource,
Property.PR_TrackII, Property.PR_TrackIIExceptionProperties FROM
(Property INNER JOIN (Sources INNER JOIN [Actual Sources] ON
Sources.S_UTID = [Actual Sources].AS_UTID) ON Property.PR_PRID =
[Actual Sources].AS_PRID) LEFT JOIN [Financing for Acquired Properties]
ON Property.PR_FinancingSource = [Financing for Acquired
Properties].[Financing for Acquired PropertiesID]

So far so good. When I run the SQL I get all records. When I run the
report I get those records that were in the query resultset.

Here's what is crazy: In the design section of the report, if I remove
one field - AS_UTID, then the output gets filtered as if I had equal
joins in all tables.

The report is GROUPED by 4 fields - the first three come from the
Property table. The last grouping appears to be the culprit, which is
done on the field called FundSource. Notice it's the result of an NZ
function since this value can be null. Access processes the NZ fine
when running the query, but then goes to lunch at report display time
somehow. Of interest, I'm using this exact same logic in at least 3
other reports with no problems - those reports also group on the field
that is sometimes null due to no match.

If I remove that fourth grouping, all is well. (Except I need that
group!!)
If I keep the fourth grouping in the report, and remove the field
AS_UTID, all is well (but I need that field).

If the AS_UTID field was in the table that sometimes has no matching
records, this would make more sense to me. I'm wondering whether it's
a matter of time before my other 3 reports quit working.

I could dump the data to a temp table and solve the problem that way,
but I'm tearing my hair out trying to figure out the logical reason why
this isn't working rather than attempting a workaround.

I'm using Access 2003 format.

The database started in Version 97, and the original reports were
created using 97. This last report was created using Version 2003
format... maybe just maybe I'm stumbling into an undocumented bug??

Thanks in advance for any light that anyone can shed on this!

Cindy

 

[Allen Browne]

Cindy, there is a bug in Access similar to what you describe. See:
Records missed by SELECT query
at:
http://allenbrowne.com/bug-10.html

The article describes a case where JOINs are based on literals. Since Access
reports create their SQL statement on the fly (based on your sorting and
grouping choices), I'm wondering if your case involving the calculated field
is yielding a similar problem.

If so, you might be able to work around it by omitting the Nz() from the
query, and doing it in the report instead. That will allow Access to group
on the actual field from the table, and hopefully to recognise the correct
data type as well. Then in the report, add a text box bound to an expression
like this:
=Nz([Financing], "No financing source entered")

Just to be sure Access is not confused about what fields you are referring
to, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect

Then compact the database:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm not sure where to begin in posting this, because I'm afraid my
"hunches" might lead the reader down a wrong path (since the path isn't
working for me...) but here goes:

I have a report that uses four tables. The output for the report
varies depending upon whether I add or remove ONE field from the report
canvas.

If I remove the field from the report canvas, I get all the records
that I would expect.

The sql for the report:

SELECT DISTINCTROW Property.PR_STREET,
Property.PR_STNO,
Sources.S_TYPE,
Sources.S_DETAIL,
[Actual Sources].AS_DATE,
[Actual Sources].AS_AMOUNT,
[Actual Sources].AS_COMMENT,
[Actual Sources].AS_PRID,
[Actual Sources].AS_UTID,
Sources.[S_Contract Number],
[Actual Sources].AS_Funding_Request,
Property.PR_Name,
Nz([Financing],"No financing source entered") AS FundSource,
Property.PR_TrackII,
Property.PR_TrackIIExceptionProperties
FROM (Property INNER JOIN (Sources INNER JOIN [Actual Sources]
ON Sources.S_UTID = [Actual Sources].AS_UTID)
ON Property.PR_PRID = [Actual Sources].AS_PRID)
LEFT JOIN [Financing for Acquired Properties]
ON Property.PR_FinancingSource = [Financing for Acquired
Properties].[Financing for Acquired PropertiesID]

 

[Cindy]

Allen Browne to the rescue once again... thanks ever so much!!

Cindy, there is a bug in Access similar to what you describe. See:
Records missed by SELECT query
at:
http://allenbrowne.com/bug-10.html

The article describes a case where JOINs are based on literals. Since Access
reports create their SQL statement on the fly (based on your sorting and
grouping choices), I'm wondering if your case involving the calculated field
is yielding a similar problem.

If so, you might be able to work around it by omitting the Nz() from the
query, and doing it in the report instead. That will allow Access to group
on the actual field from the table, and hopefully to recognise the correct
data type as well. Then in the report, add a text box bound to an expression
like this:
=Nz([Financing], "No financing source entered")

Just to be sure Access is not confused about what fields you are referring
to, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect

Then compact the database:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm not sure where to begin in posting this, because I'm afraid my
"hunches" might lead the reader down a wrong path (since the path isn't
working for me...) but here goes:

I have a report that uses four tables. The output for the report
varies depending upon whether I add or remove ONE field from the report
canvas.

If I remove the field from the report canvas, I get all the records
that I would expect.

The sql for the report:

SELECT DISTINCTROW Property.PR_STREET,
Property.PR_STNO,
Sources.S_TYPE,
Sources.S_DETAIL,
[Actual Sources].AS_DATE,
[Actual Sources].AS_AMOUNT,
[Actual Sources].AS_COMMENT,
[Actual Sources].AS_PRID,
[Actual Sources].AS_UTID,
Sources.[S_Contract Number],
[Actual Sources].AS_Funding_Request,
Property.PR_Name,
Nz([Financing],"No financing source entered") AS FundSource,
Property.PR_TrackII,
Property.PR_TrackIIExceptionProperties
FROM (Property INNER JOIN (Sources INNER JOIN [Actual Sources]
ON Sources.S_UTID = [Actual Sources].AS_UTID)
ON Property.PR_PRID = [Actual Sources].AS_PRID)
LEFT JOIN [Financing for Acquired Properties]
ON Property.PR_FinancingSource = [Financing for Acquired
Properties].[Financing for Acquired PropertiesID]

So far so good. When I run the SQL I get all records. When I run the
report I get those records that were in the query resultset.

Here's what is crazy: In the design section of the report, if I remove
one field - AS_UTID, then the output gets filtered as if I had equal
joins in all tables.

The report is GROUPED by 4 fields - the first three come from the
Property table. The last grouping appears to be the culprit, which is
done on the field called FundSource. Notice it's the result of an NZ
function since this value can be null. Access processes the NZ fine
when running the query, but then goes to lunch at report display time
somehow. Of interest, I'm using this exact same logic in at least 3
other reports with no problems - those reports also group on the field
that is sometimes null due to no match.

If I remove that fourth grouping, all is well. (Except I need that
group!!)
If I keep the fourth grouping in the report, and remove the field
AS_UTID, all is well (but I need that field).

If the AS_UTID field was in the table that sometimes has no matching
records, this would make more sense to me. I'm wondering whether it's
a matter of time before my other 3 reports quit working.

I could dump the data to a temp table and solve the problem that way,
but I'm tearing my hair out trying to figure out the logical reason why
this isn't working rather than attempting a workaround.

I'm using Access 2003 format.

The database started in Version 97, and the original reports were
created using 97. This last report was created using Version 2003
format... maybe just maybe I'm stumbling into an undocumented bug??

Thanks in advance for any light that anyone can shed on this!

Cindy