user defined function issue

[kaosyeti via AccessMonster.com]

i could use a little help with a function i'm trying to create for a textbox
on a report. the textbox is in the detail section of this report and the
field that it's tied to lists a variety of 3-character codes that i want to
translate into english. the codes are listed in a separate table so i'm
trying to use dlookup to find the name of the item using the code. so if the
field looks like this:

FE9, G80, LE5, MX0, PCH, PD5, 025, 19T, 67U
or
FE9, G80, LE5, MM5, PD5, US8, 19B, 19T, 42U
or
FE9, G80, LE5, MX0, PCH, PD5, US9, U2K, 19T, 192, 42U

then i want to use this code:

Public Function FindOptions(strInput As String) As String
Dim i As Long
Dim pos As Long
Dim strOptionName As String
Dim strLookup As String
Dim strTemp As String

i = 1
pos = 0
For i = 1 To 20
Debug.Print strInput
strTemp = Mid(strInput, pos, 3)
strLookup = DLookup("[optiondesc]", "tblnewoptions", "[optioncode] =
" & Chr(34) & strTemp & Chr(34))
If IsNull(strOptionName) = True Then
strOptionName = strLookup
Else
strOptionName = strOptionName & " " & strLookup
End If
pos = pos + 4
Next

FindOptions = strOptionName
End Function

so that i can pull the list of options names using these codes. in my
textbox i simply have this as a control source:

=findoptions([Ordered Options ])

now, here's the weird part. when i run the code, i put a break in the very
beginning of this function. when the debugger gets down to the line
beginning with "strTemp =", the code goes back to the beginning of the
function. it runs that way for each record on the report, and the debug.
print line will print the option codes correctly, but i can't isolate the
first 3-character code using the mid function because it goes from that line
back to the top. if i hover my mouse over strTemp, it tells me that it's
equal to "", not the result of the mid function.

can anyone tell what i'm missing?

 

[John Spencer]

If you have Access 2000 or later, try using the split function instead and
process through the array that is generated. UNTESTED AIRCODE follows.

Public Function FindOptions(strInput As String) As String
Dim i As Long
Dim strOptionName As String
Dim strTemp As String
Dim arString as Variant

arString = Split(strInput,",")
For i = 0 to ubound(arString)
strOptionName = NZ(DLookup("[optiondesc]", "tblnewoptions", _
"[optioncode] = """ & arString(i) &""""),"")

if strOptionName <> "" THEN
strTemp = StrTemp & " " & StrOptionName
End If

Next i

FindOptions = Trim(StrTemp)

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

On the first iteration of the For Next Loop, pos is 0. It needs to be 1.
Also, the line pos = pos + 4 should be pos + 3
Also threre are some other issues, like it will always execute 20 time and
if you hit a Null value, it will error out because you can't assign Null to
any variable type except a variant. Here is a rewrite that may work for you:

Public Function FindOptions(strInput As String) As String
Dim i As Long
Dim strTemp As String
Dim varOptDescr As Variant

For i = 1 To 77 Step 4
strTemp = Mid(strInput, i, 3)
varOptDescr = DLookup("[optiondesc]", "tblnewoptions", "[optioncode] =
'" & strTemp & "'"
If Not IsNull(varOptDescr) Then
FindOptions = varOptDescr & " " & strTemp
Exit For
End If
Next i
End Function