Skip to main content

Change Record Number

Hello, 
I have a set of data that has an assigned account number. I would like to select from this data a sample, but I wish to not select more than 5 items with the same account number. 
I've indexed the data by account number and wrote my code to add the account number to an array but then checking if the previous object in the array is the same account number if so then allowing it to select that item up to 5 times. The problem I have is that ever after indexing the record set it still cycles through the data by the record number which does not change with indexing. 
Is there a function to check if a string is contained within an array and if so how many times? 

Brian Element Sat, 12/24/2016 - 18:04

Hi Chris,

Here is an examle function that will take an array and a string value and return the number of times that string value shows in the array.  Hope this helps out a bit.

Brian

Sub Main
	Dim thisArray(5) As String
	
	thisArray(0) = "Item1"
	thisArray(1) = "Item2"
	thisArray(2) = "Item1"
	thisArray(3) = "Item1"
	thisArray(4) = "Item1"
	thisArray(5) = "Item2"
	
	MsgBox ArrayItemCount(thisArray(), "Item1")
	
End Sub

Function ArrayItemCount(ByRef myArray() As String, sCheckValue As String) As Integer
	Dim i As Integer
	Dim iCount As Integer
	
	iCount = 0
	
	For i = LBound(myArray) To UBound(myArray)
		If myArray(i) = sCheckValue Then iCount = iCount + 1
	Next i
	
	ArrayItemCount = iCount
End Function