DCount Error

[The_Doc_Man]

This makes zero sense. A UNION query is just a freakin' SQL query and you have already stated that SQL extractions of the individual legs of the UNION work OK too. UNION, to the best of my knowledge, is NOT implemented by Access. It is implemented in the SQL process, whatever it is, because UNION is an SQL "primitive."

How many "legs" are there in this UNION query? Two? More than two? How many?

 

[Snowflake68]

This makes zero sense. A UNION query is just a freakin' SQL query and you have already stated that SQL extractions of the individual legs of the UNION work OK too. UNION, to the best of my knowledge, is NOT implemented by Access. It is implemented in the SQL process, whatever it is, because UNION is an SQL "primitive."

How many "legs" are there in this UNION query? Two? More than two? How many?

It's just two legs one for each table and yes I agree I makes absolutely no sense and has been driving me mad.

 

[Frothingslosh]

For what it's worth, I think this may be an issue with Access.

I just grabbed a tool of mine (HAR HAR) that is linked to SQL Server and contains a union query. I created a textbox, used '=DCount("*","qryActivitySummary_OLD")', and received the same #ERROR result. This was using Access 2007.

This (DCount on a union query referencing a linked table) might just be a capability that wasn't added until A2013, although I don't see why it would be an issue.

 

[Frothingslosh]

In fact, I wound up doing a DCount in every single field and the results were...odd.

The attached image shows the results of DCount* plus the aforementioned DCount on each field. The weird thing is thatthe only field whose data doesn't come directly from SQL Server (as the name implies, it's a count) actually worked.

Maybe you can find or even add some calculated field to your union query and do your DCount on that field? Hell, even just adding one field with a value of 1 to each component would probably do the trick.

 

[The_Doc_Man]

And if adding a field with a constant value of 1 really DOES the trick, you can have that field exist ONLY in the query so that you don't have to do anything to the underlying table.

SELECT A, B, C, 1 AS D FROM TABLE WHERE...;

Then sum or count the "D" fields. I recall getting some aberrant results (not #Error, though) in some of my queries where the SIMPLEST solution was to add a field for summing so that I could get something on which to base a statistic I was generating.

 

[Snowflake68]

And if adding a field with a constant value of 1 really DOES the trick, you can have that field exist ONLY in the query so that you don't have to do anything to the underlying table.

SELECT A, B, C, 1 AS D FROM TABLE WHERE...;

Then sum or count the "D" fields. I recall getting some aberrant results (not #Error, though) in some of my queries where the SIMPLEST solution was to add a field for summing so that I could get something on which to base a statistic I was generating.

I tried your suggestion but still no joy with this. Would it help if I uploaded a cut down version of the application so that you can see what I am actually doing? Obviously the linked SQL table will need to be just local tables with just some dummy data as the data is confidential.

 

[Snowflake68]

In fact, I wound up doing a DCount in every single field and the results were...odd.

The attached image shows the results of DCount* plus the aforementioned DCount on each field. The weird thing is thatthe only field whose data doesn't come directly from SQL Server (as the name implies, it's a count) actually worked.

Maybe you can find or even add some calculated field to your union query and do your DCount on that field? Hell, even just adding one field with a value of 1 to each component would probably do the trick.

I tried you suggestion but it didn't work unfortunately. I had high hopes but it just wasnt to be. Just to confirm what I did; I added a virtual field to each leg of the union query with a value of just 1 and then changed the Dcount function to count that field.

Although I have a work around for this now (as said in one of my earlier replies), Ive not given up on finding a solution to this as it may help someone else in the future.