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
– Krish, Commented Jul 21, 2015 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
– Don Jewett, Commented Jul 21, 2015 at 23:37

Zaider · Accepted Answer · 2015-07-21 12:29:31Z

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, 2015 at 12:29

Zaider

2,0231 gold badge24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Douwe Over a year ago

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.

Zaider Over a year ago

Does this error occur on the line

arrFreeSingleModePorts(i) =          DCount("ID", "SplitterboxPorts", _         "SplitterboxID=i AND Mode='SingleMode' AND IsNull(DevicePortID) AND IsNull(SplitterboxPortID)")

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related