Setting a DIM value as the result of a query

[Locopete99]

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

SELECT Count(*) AS Quantity
FROM Tbl_NPPRstats
WHERE (((Tbl_NPPRstats.[Area Sent to])="Japan"));

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:

Dim Japan as integer


Japan  = Docmd.openquery "QRY_NPPRQTYJPN", acViewNormal

Any help would be appreciated.

 

[Ranman256]

queries return RECORDSETS.

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

 

[Locopete99]

Thanks Ranman,

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

 

[Minty]

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]