Calling Module function in query

[Chauhan@discussions.microsoft.com Hitesh Chauhan]

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]

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.

[Klatuu]

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]

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

[Klatuu]

No problem, just wanted the OP to be aware.