Calculating Expiry Date -

[Jojojo]

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

[June7]

The convention is to not save calculated results. To adhere to this would need to save the membership period (8 weeks, 6 months, 12 months, etc). Since you currently have only the one (8 weeks) every record would have 8 as the value. You can set this as default for the field and control on form can be locked so value can't be edited. Build report with the calculation using the field as source for the period in a textbox or in the report's RecordSource SQL statement and save it. If you change the period in future or add options, as long as the unit (weeks) is the same, no need to change formulas.

[Jojojo]

I am having a problem with the formula to calculate the date now:

I created a table MembershipLength
MembershipLengthID (autonumber)
MembershipLength

There are 2 records 8 and 10 (stands for weeks), and I created a query to add weeks behind those records. I added a combo box, calling the query, on the Registration form, and set it to default to 8 weeks.

The user can also add a # of days to the membership - so it could be an 8 week membership, with 5 extra days.

Here is the code:
=DateSerial(Year([RegistrationDate]),Month([RegistrationDate]),Day([RegistrationDate]))+[AddDays]

I can't get it to add 8 (or 10 etc) weeks to the date. - When I try referencing the MembershipLength field - it tells me I am creating a circular reference??

[June7]

First, I think the MembershipLength table might be unnecessary complication, if there are no other info fields about the MemembershipLength. You can create options in a combo or list box with the ValueList option. Just type the options as a series in the RowSource property.

Where did you put the calculation?

Try:
DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]

[Jojojo]

The reason I created that table was I want the user to be able to add weeks if needed without me. I thought the table/form option was best for that, unless there is a way to give hier some GUI to append this to the code of the value list - but that's way beyond my skill level

The calculation is being put in the unbound text box on the registration form. I tried the above and tried referencing the MembershipLength Table and I get #Name? as a result - I also tried just creating a text box on the registration form and entering the number 8 and I still ended up with the same result.

With the DateAdd function will the user still be able to run reports based on date? They way I understood what I read was no, or is it that they report has to be the run on the whole date and not just a part (llike all records from 2011)? Thx!

[June7]

The MembershipLength field has to be included in the form's RecordSource, by query join if necessary. Not sure why the unbound textbox didn't work.

The DateAdd function is used to construct a value in query or in a textbox. Do it in a query and base forms and reports on the query and the constructed field can be referenced like any field. Any part of a date can be extracted in an expression to construct a field that can be used to filter on.

If you want to provide project, I will look at.

[Jojojo]

Thank you! I set up the Query - Renewal Date - and when I run it, the dates are correct. I set the unbound text box Control Source to the Query and I get the #Name?

I've attached the project - The form is Registration. Thank again!

[June7]

The unbound textbox ControlSource is NOT set to the query. ControlSources are NEVER set to a query, they are set to a field of a form's RecordSource or an expression. You have an expression. The query Renewal Date is not included in the form's RecordSource and serves no purpose.

Why are you using DateSerial function?

I run the Registration form from the button on Attendees and it works, no #Name error.

[Jojojo]

I'll have to figure out how to include the query in the record source - but I'm wondering - where do you see the DateSerial function? the previous version I uploaded had it but I took it out bewfore I uploaded the last one...

[June7]

I knew that project was familiar - I accidentally opened the old one. Okay, you don't need the RenewalDate query. Just do that calculation in the query that is the Registration form RecordSource or in the textbox ControlSource.

You can't refer to a table/query directly in a ControlSource. ControlSource must refer to a field (a 'bound' control) of form or report RecordSource or be a calculable expression.

[Jojojo]

how to put the code is in beyond me... I can't figure this out. I am struggling with what code to put where.

1) When adding it to the Form's Recordsource, I've tried various ways to add the MembershipLength Table's MembershipLength field - and I get various errors.

2) when trying to do it just in the textbox as a calculation - when I refer to the table/field for the membershiplength, I still get the #Name? error.

Do I have to have the MembershipLength field in the Form's row source AND the textbox or 1 or the other (I understood your last post as 1 or the other)?

This is the Form's Record Source:
SELECT DISTINCTROW Registration.* FROM Members INNER JOIN Registration ON Members.AttendeeID=Registration.AttendeeID WHERE ((Registration.AttendeeID=forms!Attendees!Attendee ID));

I tried various ways to add the record from the 2nd table, I've even tried UNION and UNION ALL nased on search results for this and nothing works or gives me a variety of different errors...

This is some of what I've tried:
DateAdd("ww",MembershipLength!MembershipLength,[RegistrationDate])+[AddDays]

DateAdd("ww",[MembershipLength!MembershipLength],[RegistrationDate])+[AddDays]

DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]

...I'm lost

[Jojojo]

I think maybe I'll just make the membership length a textbox with 8 as the default in weeks. That should make it a lot easier for me...


Update
I set this combo box to a text box and bound it to the Registration Table and set it to be a default of 8. Then used this formula in the Renewal Date's unbound text box on the REgistration Form and it worked:

DateAdd("ww",[MembershipLength],[RegistrationDate])+[AddDays]

[June7]

If you want to use the MembershipLength table as source for the length data, must include it in the form's RecordSource by joining on the key fields, just like you are joining Members and Registration tables. This will make the Length field available for reference in the textbox expression.

Instead of Inner you should use Left Join - 'Show all records from Members...'