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." wrote:
> 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" wrote:
>
> > 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." wrote:
> >
> > > 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?
|
Similar Threads
