VBA Function Help Needed (1 Viewer)

[graviz]

Lookup Table
Name: "Code_Test"
Fields:
Code: A 2 Digit Text Field
Price: A number amount

Example of values in the table:

CV / 45
~R / 15

I first wrote some code to determine if any of the codes in the "Code_Test" table is in the service_code_string field of a record (as seen below). It works great however I'd like to adjust it to do some addition.

Function CodeFind(service_code_string As String) As Integer
Dim DB As DAO.Database
Dim Code_RS As DAO.Recordset
Set DB = CurrentDb()
Set Code_RS = DB.OpenRecordset("Code_Test")
Code_RS.MoveFirst
    Do While Not Code_RS.EOF
        If InStr(service_code_string, Code_RS!Code) Then
            CodeFind = 1
            Exit Function
        End If
        Code_RS.MoveNext
    Loop
CodeFind = 0
End Function

Each record has a service code string as seen below.

P2|~R|8(|5U|96|NI|#.|OB|CV|**|0$

What I'd like to do is sum up the value of each record using the code_test table. In this example I'd like the value of my function to yield 60 (45+15). I hope this makes sense. Thanks!!!

 

[pbaldy]

Try taking out the Exit line and change the other to

CodeFind = CodeFind + Code_rs!Price

 

[pr2-eugin]

This CODE should sort it out for you !

Function getSum(codeStr As String) As Long
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpLng As Long
    Dim Code_RS As DAO.Recordset
    Set Code_RS = CurrentDB.OpenRecordset("Code_Test")
    
    Do While Not Code_RS.EOF
        If InStr(codeStr, Code_RS!Code) Then tmpLng = tmpLng + Code_RS!Price
        Code_RS.MoveNext
    Loop
    getSum = tmpLng
End Function

EDIT: Way too slow there I guess ! Lol.

 

[namliam]

I wouldnt just use Instr, not just like that because it may or may not be part of a bigger string....

By adding a | in front and back, you wrap each value in |value| and then instr on |value| instead.

 

[pr2-eugin]

By adding a | in front and back, you wrap each value in |value| and then instr on |value| instead.

Interesting that you have noted that namliam. Although what happens for the First and last code, in the above example P2 and 0$ do not have a starting | or ending | respectively. So the logic might be breaking. Of course the logic is flawed in the function I have provided too; based on the concept that

I wouldnt just use Instr, not just like that because it may or may not be part of a bigger string....

The one way I can see is, maybe we can use Split function with the | as the delimiter and then loop through? Again, that will be a bit more hassle.

Well this one seems to do the trick, but again needs testing..

Function getSum(codeStr As String) As Long
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpLng As Long, tmpArr() As String
    Dim midVal As Long, iCtr As Long
    tmpArr = Split(codeStr, "|")
    For iCtr = 0 To UBound(tmpArr)
        midVal = Nz(DLookup("Price", "Code_Test", "Code = '" & tmpArr(iCtr) & "'"), -745)
        If midVal <> -745 Then tmpLng = tmpLng + midVal
    Next
    getSum = tmpLng
End Function

 

[graviz]

Try taking out the Exit line and change the other to

CodeFind = CodeFind + Code_rs!Price

This worked great! I knew it was something basic but just had a mind block. Thanks for your help!

Also thanks everyone else who chimed in.

 

[pbaldy]

Happy to help!

 

[graviz]

Happy to help!

I think I might have marked this solved a bit too soon. If I have mutiple instances of one of the codes (i.e. 2 "~r"s) it only counts one of them. It counts it as 15 when it should be 30 since there are two of them Any ideas?

 

[pr2-eugin]

I think I might have marked this solved a bit too soon. If I have mutiple instances of one of the codes (i.e. 2 "~r"s) it only counts one of them. It counts it as 15 when it should be 30 since there are two of them Any ideas?

So try the code in Post#5?

 

[namliam]

Was thinking along these lines:

Function getSum(codeStr As String) As Long
'**********************
'Code Courtesy of
'  Paul Eugin
'**********************
    Dim tmpLng As Long
    Dim Code_RS As DAO.Recordset
    Set Code_RS = CurrentDB.OpenRecordset("Code_Test")
    
    Do While Not Code_RS.EOF
        If InStr("|"& codeStr & "|", "|" & Code_RS!Code& "|") Then tmpLng = tmpLng + Code_RS!Price
        Code_RS.MoveNext
    Loop
    getSum = tmpLng
End Function

Also considered the split function.... but this should work too... unless you run into duplicate values, dont know if that might even be possible.

 

[graviz]

Interesting that you have noted that namliam. Although what happens for the First and last code, in the above example P2 and 0$ do not have a starting | or ending | respectively. So the logic might be breaking. Of course the logic is flawed in the function I have provided too; based on the concept that
The one way I can see is, maybe we can use Split function with the | as the delimiter and then loop through? Again, that will be a bit more hassle.

Well this one seems to do the trick, but again needs testing..

Function getSum(codeStr As String) As Long
[COLOR=green]'**********************[/COLOR]
[COLOR=green]'Code Courtesy of[/COLOR]
[COLOR=green]'  Paul Eugin[/COLOR]
[COLOR=green]'**********************[/COLOR]
    Dim tmpLng As Long, tmpArr() As String
    Dim midVal As Long, iCtr As Long
    tmpArr = Split(codeStr, "|")
    For iCtr = 0 To UBound(tmpArr)
        midVal = Nz(DLookup("Price", "Code_Test", "Code = '" & tmpArr(iCtr) & "'"), -745)
        If midVal <> -745 Then tmpLng = tmpLng + midVal
    Next
    getSum = tmpLng
End Function

Thanks Paul. At first glance this seems to work and catch the mutiple values. I hope right when I hit submit it I don't see an issue. Could you please take me through this code? I'd like to try to understand it versus just to copy and paste. I understand the dlookup portion but not familiar with the split function or what the -745 part is. Sorry if this is very basic.

 

[pbaldy]

That other Paul's light is off, so he may have headed for a pub. The Split() function creates an array out of the values in your string. The For/Next loop checks the value of each item in the array. The -745 is the value the Nz() function would have given the variable if the array value was not found in the table, so testing for <> -745 indicates the value was found.

Presumably he used that figuring that -745 would never be exist in the price table. An alternative would have been to leave out the Nz() function and test for Null, which is what the DLookup() would return if it couldn't find a match (the variable would have to be declared as Variant to do that).