Avatar of kaysoo
kaysoo
Flag for Malaysia asked on

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.
Microsoft Access

Avatar of undefined
Last Comment
kaysoo

8/22/2022 - Mon
John Tsioumpris

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
ASKER CERTIFIED SOLUTION
als315

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
kaysoo

ASKER
TQVM als315, this is exactly I am looking for, thumbs up!
Your help has saved me hundreds of hours of internet surfing.
fblack61