DSum on UNION ALL Query

[jibb]

Hello All,

I need to calculate the total of a union all query. It works fine if I take out the ALL in the query but with the ALL in the query it returns an error??

Any idea how I can sort this out?

 

[tehNellie]

Posting the query SQL and the Error message you're getting would help.

 

[jibb]

The union query is working fine on its own, but when I put the Dsum on a seperate form it just says "#Error" in the text box.

 

[plog]

Does the field that you are summing contain text or numbers? Just because the characters it contains are numbers, doesn't mean the field is numeric. I would force the field to be numeric in the UNION query by multiplying it by 1. For example:

SELECT *, [YourFieldNameHere]*1 AS NumericFieldName FROM FirstTableNameHere
UNION ALL
SELECT *, [YourFieldNameHere]*1 AS NumericFieldName FROM SecondTableNameHere;

 

[jibb]

It is a numeric field. What I can't work out is that when I take the "ALL" out of the query the DSum works fine, put the "ALL" back in and it returns #ERROR...