DCount from SQL Statement

[cariengon]

I'm trying to assign a count of records to an integer. I can do this fine by using a standard join query to get the results, BUT the query itself takes forever and slows down the process I'm running. So I though I could just run it as a SQL statement, but I'm not having any luck. These are the two items I've tried, but I keep getting an error. I know my syntax is off somewhere on one of these...

Dim SQLStmt as String
Dim intMatchCount as Integer

1st -

'SQLStmt = "SELECT Count([3011_BU_Contract_Amt_CR_tbl].[Primary Key]) AS [CountOfPrimary Key] " & _
' "FROM 3011_BU_Contract_Amt_CR_tbl INNER JOIN 3011_BU_Contract_Amt_DR_tbl ON ([3011_BU_Contract_Amt_CR_tbl].[Contract ID] = [3011_BU_Contract_Amt_DR_tbl].[Contract ID]) AND ([3011_BU_Contract_Amt_CR_tbl].BU = [3011_BU_Contract_Amt_DR_tbl].BU) AND ([3011_BU_Contract_Amt_CR_tbl].Credit = [3011_BU_Contract_Amt_DR_tbl].Debit)"

intMatchCount = DoCmd.RunSQL "SELECT Count([3011_BU_Contract_Amt_CR_tbl].[Primary Key]) " & _
' WHERE ([3011_BU_Contract_Amt_CR_tbl].[Contract ID] = [3011_BU_Contract_Amt_DR_tbl].[Contract ID]) And ([3011_BU_Contract_Amt_CR_tbl].BU = [3011_BU_Contract_Amt_DR_tbl].BU) And ([3011_BU_Contract_Amt_CR_tbl].Credit = [3011_BU_Contract_Amt_DR_tbl].Debit)

2nd -
intMatchCount = Count("[Primary Key]", "3011_BU_Contract_Amt_CR_tbl", _
"[3011_BU_Contract_Amt_CR_tbl].[Contract ID] = [3011_BU_Contract_Amt_DR_tbl].[Contract ID] AND [3011_BU_Contract_Amt_CR_tbl].BU = [3011_BU_Contract_Amt_DR_tbl].BU AND [3011_BU_Contract_Amt_CR_tbl].Credit = [3011_BU_Contract_Amt_DR_tbl].Debit&quot

Any ideas on how to get this to work? I know my the speed of this process would be greatly improved if I can run this in code vs. as a query.

Thanks,
Carie

 

[scking]

First of all the function is DCount not Count as your samples show; then I recommend you use parenthesis to allow your system to comprehend properly your requirements.

"(([3011_BU_Contract_Amt_CR_tbl].[Contract ID] = [3011_BU_Contract_Amt_DR_tbl].[Contract ID]) AND ([3011_BU_Contract_Amt_CR_tbl].BU = [3011_BU_Contract_Amt_DR_tbl].BU) AND ([3011_BU_Contract_Amt_CR_tbl].Credit = [3011_BU_Contract_Amt_DR_tbl].Debit))&quot
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[cariengon]

Duh.... Thanks so much!
Carie [Sunshine]