Label Expression Help

[BrettRosso]

I was wondering if anyone could help me on a labeling VB expression that took duplicate values, say from an address field, and assigned a number of how many duplicate values there are to that point. The point in my case would be a water meter attached to a parcel that has many water meters with the same address, but needs a label saying how many there are in that parcel. I would appreciate any help.

Thanks so much,

Brett Rosso

*Here is the code that I have wrote so far, that i cannot get to work because im not a professional code writer.

Function FindLabel ( [ADDRESS] )

FindLabel = Counter ( [ADDRESS] )

End Function

Function Counter (address)

Dim PATTERN, Reg_exp
Set reg_exp = New RegExp
reg_exp.Execute = True

PATTERN = "\s{23,}"
'23 max characters in the Address field

reg_exp.pattern = PATTERN

result = PATTERN.items.count

Counter = result

End Function

*I am trying it through a spatial join at the moment, but will still accept help on creating an expression.

[HeribertoMantilla_Santamaría]

Hi.

The Address have separators? If is true, better use Split Function, the Split Function divide the text in array, and you can Count the total elements, i.e.

Dim cElement As Variant
Dim Element2 As String
Dim mCount As Long

Element2 = "1 2 3 4 5 6 "
cElement = Split(Element2, " ")
mCount = UBound(cElement) // 6 Numbers

If the last element no have the space, you can add it.

Now you can use too the Mid$ function in a while i.e.

Dim tTotal As Long
Dim iCount As Long

mCount = 1
Element2 = "1 2 3 4 5 6 "
Element2 = Element2 & Element2
Element2 = Element2 & Element2
Element2 = Element2 & Element2
Element2 = Element2 & Element2
Element2 = Element2 & Element2
iCount = 0
tTotal = Len(Element2)
    
While (mCount < tTotal)
    cElement = Mid$(Element2, mCount, 23)
    iCount = iCount + 1
    mCount = mCount + 23
Wend
    
MsgBox iCount