Calculate Due Date On Form

[jereece]

I have a form (frm_Active) that reads from a query (qry_Active) based on a table (tbl_Data). The form/query/table has the following fields:

EVENT DATE
START_DATE
REVIEW_DATE
APPROVED_DATE
POST-REVIEW_DATE

When I add a new record, I would like for the default dates to be populated based on the EVENT_DATE. For example, if I enter 1/1/2006 for the EVENT_DATE, I want the

START_DATE = +7 days
REVIEW_DATE = +55 days
APPROVED_DATE = +60 days
POST-REVIEW_DATE +180 days

I tried to do this in the table as the "default value" and it would not accept it. I don't think I can do this in the query because the query is basically displaying the table and I want to be able to manually enter a different date if needed so it will be stored in the table. I tried this on the form in the "Default Value" field, but it is just blank when I enter a new record and enter the EVENT_DATE. However I am not sure I am entering the formula correct. Can someone tell me what is the best way of doing this and what the correct formula is?

Thanks,
Jim

 

[oldaf294]

je,
DON'T STORE THEM!!!! Instead (as long as your [event_date] is a datefield) in your query in a blank field type [event_date]+7 in another [event_date]+55 and on to the next for 60 and 180. That way they are "Computed" and you don't need to store them. Just pull them out of the "fieldlist" for forms and reports.

 

[jereece]

Thanks oldaf294 for your suggestion. Normally I would not store dates like these, however I have a need to. The reason is that the calculated dates are just "recommended" or default dates. I am using this database to track one time project dates. These dates must be agreed upon by the project team and managment. So while some may accept the calculated default / recommended date, others will negotiate with management to extend the dates and sometimes we can get them to commit to a shorter duration. So I must store the dates because some will need to deviate from the recommend. That is why I hoped to let the form field add a default date that I could modify if needed.

Any other suggestions is deeply appreciated.

Thanks,
Jim

 

[ansentry]

Have a look at the attached sample (I had some spare time... waiting for someone)

If it is not what you want then it may give you some ideas for your project

 

[jereece]

I might can make this work. I was hoping there would be a simple solution. Thanks for the extra effort. I really appreciate the support on this forum.

Jim

 

[ansentry]

Jim,

I was hoping there would be a simple solution

Is that a negative or positive response?

I think that the solution was very simple. If you don't want to use the combo boxes you could just change them to text boxes and type in the values (days).

 

[jereece]

Is that a negative or positive response?

Don't get me wrong, I am not being negative. I really do appreciate the help. I was just hoping that the "Default Value" in the form field could handle this without having to create additional fields.

I do have one question however, how did you get the dropdown to remember the default numbers? I did not see a default value, but somehow it is remembering the default numbers.

Again, I really appreciate your great response.

Jim

 

[ansentry]

In your last post you said:

I was just hoping that the "Default Value" in the form field could handle this without having to create additional fields.

Yes you could have the EventDate + xDays as the default but if you read (below) from your first post it says to me you want to vary the dates, that is why I put the combo boxes there. When you open a new record the defaults are there ready to calculate + event date.

So while some may accept the calculated default / recommended date, others will negotiate with management to extend the dates and sometimes we can get them to commit to a shorter duration. So I must store the dates because some will need to deviate from the recommend.

I do have one question however, how did you get the dropdown to remember the default numbers? I did not see a default value, but somehow it is remembering the default numbers.

Example cboStartPeriod;
The combo box has its data source as query based on tblPeriod.
The control source for the combo is the field (control) StartPeriod in the tblDates.

Simply what is selected in the combo (from tblPeriod) is saved to the table tblDates.

Open the form frmDataEntry look at the DatesID (mine show 6) at the top (it is greyed out)
Look at the selected period for Start Date (on My copy it is 7). Close the form and open the table tblDates look down the the datesID column until to come to 6 and then you will see that the number in the Start Period is 7. This way you DON'T store calculation. Now open the query QryDates_2 and you will see how you can create a query to do the calculation "on the fly" . The query QryDates_2 is also the source for the other 2 forms. You could also use it as the data source for a report.

Another point, you will have noticed that when you open the form frmDataEntry the DatesID is not showing 1 (First Record) that is because the query that is the source for the form has the EventDate sort order as Ascending. You can change this if you wish.

There may be a better way to do this so if any other forum users wishes to add their ideas feel free.

 

[jereece]

Yes you could have the EventDate + xDays as the default but if you read (below) from your first post it says to me you want to vary the dates, that is why I put the combo boxes there. When you open a new record the defaults are there ready to calculate + event date.

I tried putting the code "[EVENT_DATE]+60" for example in the "Default Value" section of my "APPROVED_DATE" field on the form, but it never populates the field. Is my code wrong?

I would like for the "Default Value" in the "APPROVED_DATE" field to be populated when I enter a value in the "EVENT_DATE" field, but I also want the option to manually change the value in the field by typing in another value. So I don't need to vary the default dates, but rather if the default value that is populated is not acceptable to my customer, I would like to be able to type in another value that will then be stored in the table. I hope I am making sense.

Again, I appreciate the help.
Jim

 

[ansentry]

Put this the code in the after update event of "EventDate"

e.g

Me.ApprovedDate = DateAdd("d", 60, [COLOR="Blue"][B]txtEventDate[/B][/COLOR])

Make sure you use the "Name" and not the Control.