Setting a DIM value as the result of a query (1 Viewer)

Hi Guys,

I've put this here as I think its more VBA then queries.

I have a load of queries that count the number of instances of where a record was sent to.

For example how many records were sent to department X for processing.

The Sql for this is


What I want to do is log all of these into 1 record on another table for exporting to excel for reporting exercises.

I was hoping to this with a record set.

Is there a way to DIM a value which is the result of a query?

for example the query I've given the SQL for above is Qry_NPPRQTYJPN

I know its not as simple, but is there a way to do something like the following:


Any help would be appreciated.

queries return RECORDSETS.

but if you want an INTEGER, you should use
Dcount("*","Tbl_NPPRstats","[Area Sent to])='Japan'")

Thanks Ranman,

Think I was over complicating things. Thats Exactly what I want.

Don't forget you could get all the area counts in one query, then use that as the source of an Excel export.

SELECT [Area Sent To], Count(*) AS Quantity
FROM Tbl_NPPRstats
GROUP BY [Area Sent To]