How do i resolve this MS Access Dcount error?

Question

Getting the following error.... <The expression you entered as a query parameter produced this error: 'rs!StoreID'>

rs!StoreID is an integer in the Stores table and prints out just fine in the msgbox function What am I doing wrong? I've used Dcount many times before, just not in a loop through the records in a table...

Thanks!

strSQL = "SELECT * FROM Stores"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
    rs.MoveFirst
    While (Not rs.EOF)
    
        MsgBox ("Store: " & rs!StoreID & ", Name: " & rs!FullStoreName)
        'Make sure data exists for Store in AccountBalances Table for start and end dates
        If ((DCount("*", "AccountBalances", "[RecDate] = TempVars!varDate And [StoreID] = rs!StoreID") <= 0) Or _
        (DCount("*", "AccountBalances", "[RecDate] = TempVars!varStartDate And [StoreID] = rs!StoreID") <= 0)) Then
            NoData = NoData + 1
            NoDataStoreList = NoDataStoreList & rs!FullStoreName & vbCrLf
        End If

        MsgBox ("Checking for CFS Orphans")
        'Check if any Account Numbers for selected Store have Unassigned CFS Line Item in the AccountNumbers Table
        NumRecs = DCount("*", "AccountNumbers", "[StoreID] = rs!StoreID And [CFS LineItem] = 0 And [Account Type] = '2-Assets'") _
            + DCount("*", "AccountNumbers", "[StoreID] = rs!StoreID And [CFS LineItem] = 0 And [Account Type] = '3-Liability'") _
            + DCount("*", "AccountNumbers", "[StoreID] = rs!StoreID And [CFS LineItem] = 0 And [Account Type] = '4-Net Worth'")
        If (NumRecs > 0) Then
            CFSOrphans = CFSOrphans + 1
            CFSOrphansStoreList = CFSOrphansStoreList & rs!FullStoreName & " - " & NumRecs & " Accounts" & vbCrLf
        End If

        rs.MoveNext
    Wend
End If
rs.Close
Set rs = Nothing

score 0 · Answer 1 · answered Feb 17 '22 at 15:58

0

The values must be concatenated, for example:

If ((DCount("*", "AccountBalances", "[RecDate] = TempVars!varDate And [StoreID] = " & rs!StoreID & "") <= 0) Or _
(DCount("*", "AccountBalances", "[RecDate] = TempVars!varStartDate And [StoreID] = " & rs!StoreID & "") <= 0)) Then

answered Feb 17 '22 at 15:58

Gustav

53,498
7
29
55

First - thanks for the response! When I tried this I get a different error "Data Type Mismatch in Criteria Expression" - I also got this error when I tried simply enclosed rs!StoreID with single quotes like 'rs!StoreID' On Data Types - the Store table I am looping through uses StoreID as an auto-generated index. The 'Account Balances' table I am searching has a field StoreID which is a Number format. – MikeWojcik Feb 17 '22 at 16:46
When I plug a constant in it works..."[RecDate] = TempVars!varDate And [StoreID] = 1" – MikeWojcik Feb 17 '22 at 20:08
1

OK when I "force" a TempVar to an Int using Cint() it works - forcing a local variable to an Int did not work. Its working but I really don't understand why - I hate that! TempVars!varStoreID = CInt(rs!StoreID) If ((DCount("*", "AccountBalances", "[RecDate] = TempVars!varDate And [StoreID] = TempVars!varStoreID") = 0) Or _ (DCount("*", "AccountBalances", "[RecDate] = TempVars!varDate And [StoreID] = TempVars!varStoreID") = 0)) Then NoData = NoData + 1 NoDataStoreList = NoDataStoreList & rs!FullStoreName & vbCrLf End If – MikeWojcik Feb 17 '22 at 20:29
OK. Then, of you feel so, please mark as answered. – Gustav Feb 17 '22 at 21:10
So I found a way to work around the issue but I still don't understand why I can't use the rs!StoreID in the DCount function ?? "[RecDate] = TempVars!varDate And [StoreID] = " & rs!StoreID & "" – MikeWojcik Feb 18 '22 at 04:09
Can't tell. You must debug your data. – Gustav Feb 18 '22 at 07:21

How do i resolve this MS Access Dcount error?

1 Answers1