Module Programming

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kaysooAuthor Commented:
TQVM als315, this is exactly I am looking for, thumbs up!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.