Creating function in a module 1

[Lgibo]

HELP!-I'm a newbie in Access VBA and need help in creating a new field in a query that uses a function in a module.
In this query I created a new field called TotalImpact that uses a CustomerKWH function that contains 2 arguments (called "Impact" and "InstallMonth" in the module). The value in the MonthlyImpact field (an expression) is entered as the "Impact" argument and the value in the InstallMonth is entered as the "Month" argument. The CustomerKWH function calculates based on the Month X the MonthlyImpact.
I have written the following code, but I am not able to get calculations when I run my query.

Function CustomerKWH(Impact, InstallMonth)
Dim JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC As Date

If InstallMonth = JAN Then
CustomerKWH = Impact * 12
ElseIf InstallMonth = FEB Then
CustomerKWH = Impact * 11
ElseIf InstallMonth = MAR Then
CustomerKWH = Impact * 10
ElseIf InstallMonth = APR Then
CustomerKWH = Impact * 9
ElseIf InstallMonth = MAY Then
ElseIf InstallMonth = JUN Then
CustomerKWH = Impact * 7
ElseIf InstallMonth = JUL Then
CustomerKWH = Impact * 6
ElseIf InstallMonth = AUG Then
CustomerKWH = Impact * 5
ElseIf InstallMonth = SEP Then
CustomerKWH = Impact * 4
ElseIf InstallMonth = OCT Then
CustomerKWH = Impact * 3
ElseIf InstallMonth = NOV Then
CustomerKWH = Impact * 2
ElseIf InstallMonth = DEC Then
CustomerKWH = Impact * 1

End If

End Function

Basically, I need to have this CustomerKWH function to automatically calculate the MonthlyImpact field X whatever the number, depending on the Month.
I hope this makes sense. Thanks for your help.

 

[tomschafer]

Can you post the SQL to your query?

 

[Craig0201]

LGibo,

Firstly, both Impact and InstallMonth have not been declared with a datatype. This implies they are declared as variants and could be the cause of your trouble.

I have assumed Impact to be an integer, however, it could easily be replaced by single or double if it is a decimal.

Also, surely you hold install date in the tables rather than install month?

If so, the code should look rather more like:

Function CustomerKWH(Impact as Integer, InstallDate as Date)

CustomerKWH=(13-Month(InstallDate))*Impact

End Function

The long If statement simply reverses the number taken by a month i.e. December is 1, not 12 and January is 12, not 1. This can be achieve in maths by taking the month number from 13. Hence no need for long code. You can get the month using the month function, hence you only need to pass in the install date.

HTH

Craig

 

[Lgibo]

Craig,

It worked! Thanks very much for your help.

Lisa

 

[Craig0201]

Glad to be of service.

Craig