Using DCount in a For Loop with VBA (Microsoft Access)

Question

I'm having a problem with DCount in VBA.

I'm trying to fill an array with values calculated by Dcount. One of the criteria I use in the Dcount I want to get from a different array, this gives the problem:

Dim i As Integer
For i = 0 To (intNumberOfSplitterboxes - 1)
    Dim arrFreeSingleModePorts(243) As Variant
    arrFreeSingleModePorts(i) = 
        DCount("ID", "SplitterboxPorts", _
        "SplitterboxID=i AND Mode='SingleMode' AND IsNull(DevicePortID) AND IsNull(SplitterboxPortID)")
Next

I get error 2471

declare your array variable outside the loop. DCount has incorrect string see below answer — Krish, Jul 21 '15 at 12:33
Can you run this query successfully? SELECT Count(ID) FROM SplitterboxPorts WHERE SplitterboxID=1 AND Mode='SingleMode' AND IsNull(DevicePortID) AND IsNull(SplitterboxPortID) — Don Jewett, Jul 21 '15 at 23:37

score 0 · Answer 1 · answered Jul 21 '15 at 12:29

0

You have included your i variable inside of your string. To use the actual value of i in your string you need to concatenate them together like this: "SplitterboxID=" & i & " AND Mode='SingleMode' AND IsNull(DevicePortID) AND IsNull(SplitterboxPortID)"

answered Jul 21 '15 at 12:29

Zaider

1,950
1
21
31

Thank you for your response. I tried your solution. I understand what the problem is. But now I get a different error: Error 13, types don't match. – Douwe Jul 21 '15 at 12:51
Does this error occur on the line `arrFreeSingleModePorts(i) = DCount("ID", "SplitterboxPorts", _ "SplitterboxID=i AND Mode='SingleMode' AND IsNull(DevicePortID) AND IsNull(SplitterboxPortID)")` – Zaider Jul 21 '15 at 14:01

Using DCount in a For Loop with VBA (Microsoft Access)

1 Answers1