Hi,
I am having a user defined function in module. i want to call in my select
statement. is it possible. if yes How?

Function Slab(SlabVal As Integer)
If SlabVal > 0 And SlabVal <= 20 Then
Slab = 1
ElseIf SlabVal > 20 And SlabVal <= 50 Then
Slab = 2
ElseIf SlabVal > 50 And SlabVal <= 100 Then
Slab = 3
ElseIf SlabVal > 100 Then
Slab = 4
End If
End Function

the above function i want to call in select statement like

select SlbValue,slab(slbvalue) from Incentive


regards,
hiteshc

Try this:

Make sure your Function is Public, like this:

Public Function Slab(SlabVal As Integer) As Integer
If SlabVal > 0 And SlabVal <= 20 Then
Slab = 1
ElseIf SlabVal > 20 And SlabVal <= 50 Then
Slab = 2
ElseIf SlabVal > 50 And SlabVal <= 100 Then
Slab = 3
ElseIf SlabVal > 100 Then
Slab = 4
End If
End Function

Now, select like this:

SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive

In the query builder, the Slab field will look like this:

Slab:Slab([SlbValue])

In addition, if SlbValue could ever be null, the function variables will
need to be Variant types instead of Integers, and you will need to handle the
null value somewhere in the If...ElseIf statement.

"Hitesh Chauhan" wrote:

> Hi,
> I am having a user defined function in module. i want to call in my select
> statement. is it possible. if yes How?
>
> Function Slab(SlabVal As Integer)
> If SlabVal > 0 And SlabVal <= 20 Then
> Slab = 1
> ElseIf SlabVal > 20 And SlabVal <= 50 Then
> Slab = 2
> ElseIf SlabVal > 50 And SlabVal <= 100 Then
> Slab = 3
> ElseIf SlabVal > 100 Then
> Slab = 4
> End If
> End Function
>
> the above function i want to call in select statement like
>
> select SlbValue,slab(slbvalue) from Incentive
>
>
> regards,
> hiteshc

Brian, I believe the way you have it coded it will choke on a circular
reference.
It is the Slab as Slab part:
SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive

It may be necessary to use a different name:
SELECT Incentive.SlbValue, Slab([SlbValue]) As SlabType from Incentive
--
Dave Hargis, Microsoft Access MVP


"Brian" wrote:

> Try this:
>
> Make sure your Function is Public, like this:
>
> Public Function Slab(SlabVal As Integer) As Integer
> If SlabVal > 0 And SlabVal <= 20 Then
> Slab = 1
> ElseIf SlabVal > 20 And SlabVal <= 50 Then
> Slab = 2
> ElseIf SlabVal > 50 And SlabVal <= 100 Then
> Slab = 3
> ElseIf SlabVal > 100 Then
> Slab = 4
> End If
> End Function
>
> Now, select like this:
>
> SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
>
> In the query builder, the Slab field will look like this:
>
> Slab:Slab([SlbValue])
>
> In addition, if SlbValue could ever be null, the function variables will
> need to be Variant types instead of Integers, and you will need to handle the
> null value somewhere in the If...ElseIf statement.
>
> "Hitesh Chauhan" wrote:
>
> > Hi,
> > I am having a user defined function in module. i want to call in my select
> > statement. is it possible. if yes How?
> >
> > Function Slab(SlabVal As Integer)
> > If SlabVal > 0 And SlabVal <= 20 Then
> > Slab = 1
> > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > Slab = 2
> > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > Slab = 3
> > ElseIf SlabVal > 100 Then
> > Slab = 4
> > End If
> > End Function
> >
> > the above function i want to call in select statement like
> >
> > select SlbValue,slab(slbvalue) from Incentive
> >
> >
> > regards,
> > hiteshc

Good point. That's what I get for posting a response in the middle of the
night.

"Klatuu" wrote:

> Brian, I believe the way you have it coded it will choke on a circular
> reference.
> It is the Slab as Slab part:
> SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
>
> It may be necessary to use a different name:
> SELECT Incentive.SlbValue, Slab([SlbValue]) As SlabType from Incentive
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Brian" wrote:
>
> > Try this:
> >
> > Make sure your Function is Public, like this:
> >
> > Public Function Slab(SlabVal As Integer) As Integer
> > If SlabVal > 0 And SlabVal <= 20 Then
> > Slab = 1
> > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > Slab = 2
> > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > Slab = 3
> > ElseIf SlabVal > 100 Then
> > Slab = 4
> > End If
> > End Function
> >
> > Now, select like this:
> >
> > SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
> >
> > In the query builder, the Slab field will look like this:
> >
> > Slab:Slab([SlbValue])
> >
> > In addition, if SlbValue could ever be null, the function variables will
> > need to be Variant types instead of Integers, and you will need to handle the
> > null value somewhere in the If...ElseIf statement.
> >
> > "Hitesh Chauhan" wrote:
> >
> > > Hi,
> > > I am having a user defined function in module. i want to call in my select
> > > statement. is it possible. if yes How?
> > >
> > > Function Slab(SlabVal As Integer)
> > > If SlabVal > 0 And SlabVal <= 20 Then
> > > Slab = 1
> > > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > > Slab = 2
> > > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > > Slab = 3
> > > ElseIf SlabVal > 100 Then
> > > Slab = 4
> > > End If
> > > End Function
> > >
> > > the above function i want to call in select statement like
> > >
> > > select SlbValue,slab(slbvalue) from Incentive
> > >
> > >
> > > regards,
> > > hiteshc

No problem, just wanted the OP to be aware.
--
Dave Hargis, Microsoft Access MVP


"Brian" wrote:

> Good point. That's what I get for posting a response in the middle of the
> night.
>
> "Klatuu" wrote:
>
> > Brian, I believe the way you have it coded it will choke on a circular
> > reference.
> > It is the Slab as Slab part:
> > SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
> >
> > It may be necessary to use a different name:
> > SELECT Incentive.SlbValue, Slab([SlbValue]) As SlabType from Incentive
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Brian" wrote:
> >
> > > Try this:
> > >
> > > Make sure your Function is Public, like this:
> > >
> > > Public Function Slab(SlabVal As Integer) As Integer
> > > If SlabVal > 0 And SlabVal <= 20 Then
> > > Slab = 1
> > > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > > Slab = 2
> > > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > > Slab = 3
> > > ElseIf SlabVal > 100 Then
> > > Slab = 4
> > > End If
> > > End Function
> > >
> > > Now, select like this:
> > >
> > > SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
> > >
> > > In the query builder, the Slab field will look like this:
> > >
> > > Slab:Slab([SlbValue])
> > >
> > > In addition, if SlbValue could ever be null, the function variables will
> > > need to be Variant types instead of Integers, and you will need to handle the
> > > null value somewhere in the If...ElseIf statement.
> > >
> > > "Hitesh Chauhan" wrote:
> > >
> > > > Hi,
> > > > I am having a user defined function in module. i want to call in my select
> > > > statement. is it possible. if yes How?
> > > >
> > > > Function Slab(SlabVal As Integer)
> > > > If SlabVal > 0 And SlabVal <= 20 Then
> > > > Slab = 1
> > > > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > > > Slab = 2
> > > > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > > > Slab = 3
> > > > ElseIf SlabVal > 100 Then
> > > > Slab = 4
> > > > End If
> > > > End Function
> > > >
> > > > the above function i want to call in select statement like
> > > >
> > > > select SlbValue,slab(slbvalue) from Incentive
> > > >
> > > >
> > > > regards,
> > > > hiteshc