UNION ALL causing #Error when used with DSUM

[stephena]

Hello all, hope someone can help.

I recently built a report showing the distribution of money in and out
of the company. Simplified version of which looks like:

DECEMBER 2004
TELEPHONE SALES: INTERNET: TOTAL:
£1200 £1000 £2200

I then built a sub-report showing adjustments, eg:

ADJUSTMENTS
TELEPHONE SALES: INTERNET: TOTAL:
Refund#1 £12 £1 £13
Refund#2 £2 £10 £12

Finally I created an overall totals for each column using the DSUM
function. Everything worked great. The sub-report is based on a UNION
query, and I realised that this was removing duplicates records, when
obviously in this context they should remain. I therefore changed the
underlying query to a UNION ALL query. This fixed the query in the
respect that it now returns all of the necessary records, however in
the report fields that reference the query using DSUM, the result
returned is '#Error'. I cannot see why this would be. What have I
done wrong? Or is it a bug? Any guidance greatly appreciated.

[Bas Cost Budde]

st******@isoplan.co.uk wrote:

the report fields that reference the query using DSUM, the result
returned is '#Error'. I cannot see why this would be. What have I
done wrong? Or is it a bug? Any guidance greatly appreciated.

Do check if the control name collides with a field name. If so, rename
the control and see whether the #error goes away.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[stephena]

It is definately to do with the UNION ALL command. If I go into the
underlying query and remove the word 'ALL' report works fine, error
goes away. Put 'ALL' back in and the error is back.
As a workaround I have added a new unique field to my underlying query,
so that no duplicates are returned, and I can use UNION rather than
UNION ALL. Seems more complicated than necessary, but it works.
Be interested to hear if anyone else out there has had problems with
this, and how they solved it.

[Bas Cost Budde]

st******@isoplan.co.uk wrote:

It is definately to do with the UNION ALL command. If I go into the
underlying query and remove the word 'ALL' report works fine, error
goes away. Put 'ALL' back in and the error is back.

Okay. What if you don't use DSum in the report, but grouping? In a group
footer you can use Sum([fieldname]).

Access does several things behind our backs when creating the datasource
for a report. Let's try several approaches in return :-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Allen Browne]

Access often misunderstands the data type of calculated fields.

Try typecasting the field in the first SELECT statement of the UNION query,
e.g.:
CCur(Nz(DSum("SomeField", "SomeTable), 0))

More info:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<st******@isoplan.co.uk> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

It is definately to do with the UNION ALL command. If I go into the
underlying query and remove the word 'ALL' report works fine, error
goes away. Put 'ALL' back in and the error is back.
As a workaround I have added a new unique field to my underlying query,
so that no duplicates are returned, and I can use UNION rather than
UNION ALL. Seems more complicated than necessary, but it works.
Be interested to hear if anyone else out there has had problems with
this, and how they solved it.