Problem Passing text varaible to DLookup

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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
 
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
Click to expand...

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
 
Thanks for your response. Once you pointed it out, I saw where I was wrong.
Appreciate the help.

Scott

Duncan Bachen said:
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
Click to expand...

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

---------------------
Duncan Bachen (dbachen@NOSPAM_olehansen.com)
Director of IT
Ole Hansen and Sons Inc.
---------------------
Click to expand...
 
Back
Top