Problem Passing text varaible to DLookup

message from Scott on 21 May 2004

I'm trying to use Dlookup to generate output for a field
on a report. The Dlookup procedure is being called by a
function on the report. I know the variable is being
passed correction to the code module, my problem seems to
be getting Dlookup to read the variable correctly. If I
insert static entries where I'm trying to use a variable
the right results are returned. If anyone can look at
the following simple code and see where I'm going wrong
it will help.

Function PROVIDERINQUIRY(PROVIDERNUMBER)

If IsNull(DLookup("[NAME]", "PROVIDER", "[PROVID]
+'PROVIDERNUMBER'")) Then
PROVIDERINQUIRY = PROVIDERNUMBER
Else
PROVIDERINQUIRY = (DLookup
("[HEADNAME]", "PROVIDER", "[PROVID]='PROVIDERNUMBER'"))
End If

End Function

Duncan Bachen replied to Scott on 21 May 2004

You've got several potential problems here.
1) You don't define the type of parameter being passed to the
function. Is it a number? Is it a string?
2) Since it's not defined in the function, I'm going to assume that
it's a string, because you are enclosing it in quotes as part of your
DLookup. If it's not a string, then there's one problem right there.
Though your naming convention of calling it a number wants to make me
believe its a number
3) Your first "if" clause is using a +, when it looks like you want to
check the value of the parameter passed, or you want to surroung it
with quotes. Trying to compare your two Dlookups is confusing.
4) Why do the DLookup twice? It's a performance hit. Let me take that
back. It looks like you are checking the value of "Name" once and
"HeadName" the second time.
5) Avoid using the fieldname "Name", as it's a reserved word. You've
got it in brackets, which will help, but you should get out of the
habit.

Here's how I would rewrite the function (assuming I understand what
you are trying to accomplish)

Function ProviderInquiry(sProviderNumber as String) as String
Dim vProviderTemp as Variant

ProviderTemp = DLookup("[NAME]", "PROVIDER", "[PROVID]='" &
sProviderNumber & "'")

If IsNull(vProviderTemp) Then
vProviderTemp = sProviderNumber
Else
vProviderTemp= DLookup("[HEADNAME]", "PROVIDER", "[PROVID]='" &
sProviderNumber & "'")
End If

ProviderInquiry = CStr(vProviderTemp)
End Function

If it's a number and not a string, you would concatenate it without
the quotes, such as:
ProviderTemp = DLookup("[NAME]", "PROVIDER", "[PROVID]=" &
sProviderNumber)

You'd also have to change the date type of the passed parameter and
the return value of the function.
-D

Scott G. Krueger replied to Duncan Bachen on 24 May 2004

Thanks for your response. Once you pointed it out, I saw where I was wrong.
Appreciate the help.

Scott

"Duncan Bachen" <dbachen@NOSPAM_olehansen.com> wrote in message
news:l7osa05so7g51f55brq60uguo2isq2udo7@4ax.com...

Archived message: Problem Passing text varaible to DLookup (Microsoft Access)