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
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

Zaider Zaider · Accepted Answer · 2015-07-21T12:29:31

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)"

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

1 Answers