Using Select Sum .... queries

[tonygg]

Hi. I am trying to use a query in VBA to find out the number of records and the sum of the record values in a table.

The SQL is:

SELECT Sum(TONYWorldPayTransactions.[Merchant Commission Amount]) AS Expr1, Count(TONYWorldPayTransactions.[Merchant Commission Amount]) AS Expr2 FROM TONYWorldPayTransactions


WHERE (((TONYWorldPayTransactions.[Batch Id])>=6 And (TONYWorldPayTransactions.[Batch Id])<=26) AND ((TONYWorldPayTransactions.WPBarclayFlag)='B') AND ((TONYWorldPayTransactions.[Merchant Commission Amount])<>0) AND ((TONYWorldPayTransactions.[Event Type])='SETTLED') AND ((TONYWorldPayTransactions.CountryofCard)='GB') AND ((TONYWorldPayTransactions.CardType)='VISA_CREDIT-SSL'));

In native access query design it returns 2 values. The sum of the merchant commission amounts and the number of records. All good. My issue is I don't know how to retrieve/use these values in a vba sub routine. I just need to display the resulting 2 values

I tried:

Set rsOrders = Nothing

SQLOrderString = "SELECT Sum(TONYWorldPayTransactions.[Merchant Commission Amount]) AS Expr1, Count(TONYWorldPayTransactions.[Merchant Commission Amount]) AS Expr2" & _
" WHERE TONYWorldPayTransactions.[Batch Id] >= " & Me.PaymentProviderBatchNumber & " AND TONYWorldPayTransactions.[Batch Id] <= " & Me.PaymentProviderBatchTo & _
" AND (TONYWorldPayTransactions.[WPBarclayFlag] = 'B' AND TONYWorldPayTransactions.[Merchant Commission Amount] <> 0 AND TONYWorldPayTransactions.[Event Type] = 'SETTLED' AND TONYWorldPayTransactions.[CountryofCard] = 'GB' AND TONYWorldPayTransactions.[CardType] = 'ECMC_CREDIT-SSL');"

Set rsOrders = CurrentDb.OpenRecordset(SQLOrderString)

Me.MCCreditAmount = rsOrders![Merchant Commission Amount]

However this gets a compile error on the final line. I guess this is not the correct method.

I would be very grateful if someone could help me get the VBA sorted out.

Many thanks

[June7]

The expression is not referencing the field in the query. Need to reference the alias field name.

Me.MCCreditAmount = rsOrders!Expr1

[tonygg]

Great Thankyou.