I am working on a database for a Gym, registering classes.
The Registration Form has the user input the Registration Date into a bound Text box. Each Registration is 8 weeks by default. But there can be any number of days added to the membership, for things like the client getting an extra week or 2 of classes for referrals etc. So, there is also another bound text box for AddDays - so they can enter 7 days. I then have an unbound text box to calculate the Renewal (Expiry) Date:
=DateSerial(Year([RegistrationDate]),Month([RegistrationDate]),Day([RegistrationDate])+55)+[AddDays]
Everything I read said to put calculations in unbound text boxes and not to store them in a table. But everytime I want to run a report, query etc that includes the Expiring Date, I have to put the above formula in or I get blank data. Also - I can't run a date range query for renewal dates (renewals between Sept 1 and 30th for example).
I am just wondering - is there a better way to do this? What if down the road - memberships become 10 weeks. I would have to change the formula everywhere, but then wouldn't it it change all of the past registrations as well? Should I create a drop down list for the membership length? It would only have 8 weeks in it for now, but would allow for expansion? Help