Custom Function in a Select Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Instead of using the built in IIF function, I wanted to try my hand at
building a custom function using SELECT CASE instead.

I want my function to look at the [Legal Entity Cd] field, and perform an
action based on the legal entity code contained within this field. The
Select Case would look something like this:

Function Entity()
Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

When I try to incorporate this function into my select query, I get a
"External Name not Defined" error. Any ideas?
 
Hi Kirk,

The function "Entity" have to be public. I usually put them in the "Module"
area.
You also have pass the [Legal Entity Cd] as its parameter.

Function Entity(Arg1 as variant) as variant
Select Case Arg1
 
I put the function in a Module, and tried passing in [Legal Entity Cd] as a
parameter. I'm getting an error message saying "compile error - expected
identifier" Here's my code:

Public Function Entity([Legal Entity Cd]As Variant)As Variant

Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

End Function

JL said:
Hi Kirk,

The function "Entity" have to be public. I usually put them in the "Module"
area.
You also have pass the [Legal Entity Cd] as its parameter.

Function Entity(Arg1 as variant) as variant
Select Case Arg1
.
.
.
End Select
End Function

Hope this helps.


Kirk P. said:
Instead of using the built in IIF function, I wanted to try my hand at
building a custom function using SELECT CASE instead.

I want my function to look at the [Legal Entity Cd] field, and perform an
action based on the legal entity code contained within this field. The
Select Case would look something like this:

Function Entity()
Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

When I try to incorporate this function into my select query, I get a
"External Name not Defined" error. Any ideas?
Click to expand...
Click to expand...
 
Hi Kirk,

Well, this is how it should be done.

In your query calling the function,
select .., Entity([Legal Entity Cd]), ...
from ...
.....

In your Module which containt the function "Entity",
Public Function Entity(EntityCd As Variant)As Variant

Select Case EntityCd
Case "10"
Entity = "NSP-MN"
Debug.Print "NSP-MN"
Case "11"
Entity = "NSP-WI"
Debug.Print "NSP-WI"
Case Else
Entity = "Other"
Debug.Print "Other"
End Select
End Function

Hope this is more clear for you.

Kirk P. said:
I put the function in a Module, and tried passing in [Legal Entity Cd] as a
parameter. I'm getting an error message saying "compile error - expected
identifier" Here's my code:

Public Function Entity([Legal Entity Cd]As Variant)As Variant

Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

End Function

JL said:
Hi Kirk,

The function "Entity" have to be public. I usually put them in the "Module"
area.
You also have pass the [Legal Entity Cd] as its parameter.

Function Entity(Arg1 as variant) as variant
Select Case Arg1
.
.
.
End Select
End Function

Hope this helps.


Kirk P. said:
Instead of using the built in IIF function, I wanted to try my hand at
building a custom function using SELECT CASE instead.

I want my function to look at the [Legal Entity Cd] field, and perform an
action based on the legal entity code contained within this field. The
Select Case would look something like this:

Function Entity()
Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

When I try to incorporate this function into my select query, I get a
"External Name not Defined" error. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Works perfectly. Thanks for your help!

JL said:
Hi Kirk,

Well, this is how it should be done.

In your query calling the function,
select .., Entity([Legal Entity Cd]), ...
from ...
....

In your Module which containt the function "Entity",
Public Function Entity(EntityCd As Variant)As Variant

Select Case EntityCd
Case "10"
Entity = "NSP-MN"
Debug.Print "NSP-MN"
Case "11"
Entity = "NSP-WI"
Debug.Print "NSP-WI"
Case Else
Entity = "Other"
Debug.Print "Other"
End Select
End Function

Hope this is more clear for you.

Kirk P. said:
I put the function in a Module, and tried passing in [Legal Entity Cd] as a
parameter. I'm getting an error message saying "compile error - expected
identifier" Here's my code:

Public Function Entity([Legal Entity Cd]As Variant)As Variant

Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

End Function

JL said:
Hi Kirk,

The function "Entity" have to be public. I usually put them in the "Module"
area.
You also have pass the [Legal Entity Cd] as its parameter.

Function Entity(Arg1 as variant) as variant
Select Case Arg1
.
.
.
End Select
End Function

Hope this helps.


:

Instead of using the built in IIF function, I wanted to try my hand at
building a custom function using SELECT CASE instead.

I want my function to look at the [Legal Entity Cd] field, and perform an
action based on the legal entity code contained within this field. The
Select Case would look something like this:

Function Entity()
Select Case [Legal Entity Cd]
Case "10"
Debug.Print "NSP-MN"
Case "11"
Debug.Print "NSP-WI"
Case Else
Debug.Print "Other"
End Select

When I try to incorporate this function into my select query, I get a
"External Name not Defined" error. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Hi Kirk,

Sorry that I did not mention another way. There are so many way to do the
same stuff.
Instead of use IIF or function, you can also use a look up table to join
with your table to get the value that you need. That is my prefer method to
handle lookup code.
Just for your information.
 
Back
Top