programming a function into Access VB

[cubby1223]

I'm taking data from one source and manipulating it in Access. It comes in an odd formatting and I'm not quite sure how to work with it.

The input data is a string with numbers separated by spaces. I want to get a sum total of the numbers. This is the correct function if written up in coldfusion, but I'd like to do the calculation in Access:

<cfscript>
function getAmount(_amountstring) {
var i=0;
var returnAmount=0;
for(i=1;i lte listLen(_amountstring," ");i=i+1) {
returnAmount=returnAmount+val(listGetAt(_amountstring,i," "));
}
return returnAmount;
}
</cfscript>

 

[KLin]

Public Function SumString(ByVal num As String)
Dim i As Long, i2 As Long, CurNum As String, ArrNum(500) As Long, SumNum as Long
i2 = 1
For i = 1 To Len(num)
If Mid(num, i, 1) <> " " Then
CurNum = CurNum & Mid(num, i, 1)
Else
ArrNum(i2) = CLng(CurNum)
i2 = i2 + 1
CurNum = ""
End If
If i = Len(num) Then
ArrNum(i2) = CLng(CurNum)
End If
Next i

For i2 = 1 To 500
SumNum = SumNum + ArrNum(i2)
Next i2
SumString = SumNum
End Function

That's one way to do it. Then call sumstring passing in the number string(ie from the immdiate window type in ? sumstring("1 2 3 4") to get a sum of 10)


EDIT: the first section is parsing the string and getting the numeric values, then loading them into an array. The second section just loops through the array, adding any non-zero values to a variable, then it returns that variable's value.

 

[Crusty]

Or you could use string.split and skip that entire first loop.

Also KLin, why are you checking if the number is 0 or not?

Worst case assuming no numbers are 0, you will be doing n comparisons, and n addition operations.
Best case assuming all numbers are 0, you will do n comparisons, and 0 addition operations.
Avg case assuming 50% of numbers are 0, you will do n comparisons, and n * .5 additions.

Now, if you didn't check for 0, no matter what the input is you will only perform n addition operations.

Granted, it's only a linear function but with VBA any speed increase helps IMO. Not knowing the context of the Function makes it hard to determine if it's worth optimizing code in such a way though.

 

[KLin]

Originally posted by: Crusty
Or you could use string.split and skip that entire first loop.

Also KLin, why are you checking if the number is 0 or not?

Worst case assuming no numbers are 0, you will be doing n comparisons, and n addition operations.
Best case assuming all numbers are 0, you will do n comparisons, and 0 addition operations.
Avg case assuming 50% of numbers are 0, you will do n comparisons, and n * .5 additions.

Now, if you didn't check for 0, no matter what the input is you will only perform n addition operations.

Granted, it's only a linear function but with VBA any speed increase helps IMO. Not knowing the context of the Function makes it hard to determine if it's worth optimizing code in such a way though.

Sounded like the op wanted something quick and dirty 😉. And you're right, it's not necessary to check for 0.

 

[cubby1223]

Thanks for the all the help. I don't work with Access much anymore, since most all my work went online through ColdFusion. Not concerned with optimization. One day I'll really have to learn it, right now my method of speeding up the process is buying a faster cpu. 😉 I had a college course once on data structures and optimization, but I didn't do too well in it. 😱