Calling Module function in query

H

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
 
B

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.
 
K

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 said:
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 said:
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
 
B

Brian

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

Klatuu said:
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 said:
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 said:
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
 
K

Klatuu

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


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

Klatuu said:
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 said:
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.

:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top