Pass a VBA select query result to VBA variable?

[drazeni]

I have constructed a select query in VBA as follows:

Dim invcounttypeqdf As QueryDef
strSQL = "SELECT [PreOrders details].preorderdet_invoice_no, [PreOrders details].preorderdet_type, Count([PreOrders details].preorderdet_type) AS " &

[Counter]

 & " " _
    & "FROM [PreOrders details] " _
    & "GROUP BY [PreOrders details].preorderdet_invoice_no, [PreOrders details].preorderdet_type " _
    & "HAVING ((([PreOrders details].preorderdet_invoice_no)=" & [var_invoice_number] & ") AND (([PreOrders details].preorderdet_type)=""PLATE""));"

    Set invcounttypeqdf = dbs.CreateQueryDef("invcounttypeqry", strSQL)
    DoCmd.OpenQuery "invcounttypeqry"
    msgbox

counter

    DoCmd.Close acQuery, "invcounttypeqry"

Basically, what I need is to count the number of occurrences of "plate" in the preorderdet_type field for a specific invoice number (which I manage to pass to the query using the variable [var_invoice_number]).

My problem is that I cannot return the count back to variable counter which you see I've used "&" to make it stand out of the SQL string.

The value I get is 0 and I notice when I look at the query in design view, the field is renamed to "0". It seems I'm catching the field name instead of the value.

Any suggestions?

Thanx "All is not as it seems"

 

[jhall156]

You could use a recordset variable and set it to your query
then use nCounter = YourRecordset.Fields("counter&quot.Value to
get the value. JHall

 

[drazeni]

I have tried the recordset method and it worked. What bugs me is that I can pass a variable to the query, but cannot get values back from the queries....

Anyway, such is life.

Thanks for your response Jhall156. "All is not as it seems"