Module Programming

kaysoo used Ask the Experts™
Hi expert,

I am using Access 2003, I used the following IIF function in query to generate data of growing stage of certain animals, can anyone help me to create a module instead of using query?
I hv to use module function due to a lengthy possibility of the result outcome and query has limited text input allowed. The following is the max allowed in query column.
I need the full module programming code thx.

[DaysOfLife] is calculated from Today's Date minus individual D.O.B. in my existing query

GrowingStage: IIf([DaysOfLife] Between 1 And 15,"1-15 Days",IIf([DaysOfLife] Between 16 And 30,"16-30 Days",IIf([DaysOfLife] Between 31 And 45,"31-45 Days",IIf([DaysOfLife] Between 46 And 60,"46-60 Days",IIf([DaysOfLife] Between 61 And 80,"7cm",IIf([DaysOfLife] Between 81 And 100,"13cm",IIf([DaysOfLife] Between 101 And 150,"17cm",IIf([DaysOfLife] Between 151 And 200,"26cm",IIf([DaysOfLife] Between 201 And 250,"33cm",IIf([DaysOfLife] Between 251 And 300,"42cm",IIf([DaysOfLife] Between 301 And 350,"48cm",IIf([DaysOfLife] Between 351 And 750,"Adult",IIf([DaysOfLife]>750,"Maturity")))))))))))))

Please help to show how to create a Public Function in Module thx.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Well with so many IIFs you asking for trouble...better to forget them and try to work on a table oriented programming philosophy.
So design a table that will hold conditions and result e.g
ConditionLowLimit ConditionUpperLimit           ResultValue
251                                        300                             42 cm
301                                        350                            48 cm
Then construct a function that reads this table and accordingly produces the value
Public Function GrowingStage( DoB as date) as string
Dim Age as integer
Age = Datediff("d", DoB, Date())
IIF Age > 750 then
	GrowingStage = "Mature"
Elseif Age > 350 then
	GrowingStage = "Adult"
Elseif Age > 300 then
	GrowingStage = "48cm"
Elseif Age > 250 then
	GrowingStage = "42cm"
Elseif Age > 200 then
	GrowingStage = "33cm"
Elseif Age > 150 then
	GrowingStage = "26cm"
Elseif Age > 100 then
	GrowingStage = "17cm"
Elseif Age > 80 then
	GrowingStage = "13cm"
Elseif Age > 60 then
	GrowingStage = "7cm"
Elseif Age > 45 then
	GrowingStage = "46-60 Days"
Elseif Age > 30 then
	GrowingStage = "31-45 Days"
Elseif Age > 15 then
	GrowingStage = "16-30 Days"
        GrowingStage = "1-15 Days"
End if
End Function

Open in new window


TQVM als315, this is exactly I am looking for, thumbs up!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial