Default value of date field (Access 2003)

Topic

New Reply

I have a table that includes a Date field (I’ll call it Date_field_1 here just to simplify things). I have a second date field (in the same table) that I’ll call Date_field_2.
Date_field_1 already has data in it. I need Date_field_2 to automatically default to a value that is Date_field_1 plus 2 years. For instance, if Date_field_1 has a value of 2/15/05, I need Date_field_2 to automatically default to 2/15/07. How can I make this the default?

Reply | Quote

Replies

In your form where you populate Date_field_1 Use the AfterUpdate Event to do the following

Private Sub Date_field_1_AfterUpdate()

Me.Date_field_2 = DateAdd(“yyyy”, 2, Me.Date_field_1)

End Sub

Reply | Quote

Sorry to be difficult. I’m sure this is an easy issue for most of you, but I am not experienced in this particular area of Access. Where exactly are you inputting that? I am looking in the Properties information for Date_field_2, in the Default value field. Is there a way to program the Default value to be 2 years after the value in Date_field_1? Or do I have to do some sort of update query to populate Date_field_2? (I want to try to make this a default value rather than having the user periodically run an update query to populate Date_field_2 because I want to ensure that if there is a value in Date_field_1, there is always a value in Date_field_2. Does that make sense?

Reply | Quote

Can you tell me how you populate your table. Do you use a form?

Reply | Quote

Not necessarily. Most of the records already have data in Date_field_1. For future records, sometimes the user is typing directly into a table. Sometimes she is going to use a form. So the main thing I’m worried about is getting the new field that is currently blank (Date_field_2) populated automatically based on the value that is already in Date_field_1. There are already MANY records in the table, and all already have a date in Date_field_1. I need to have Date_field_2 automatically populated based on the value in Date_field_1, regardless of whether the data is already filled in or whether it will be added or updated in the future. Does that make sense? Thank you in advance for your patience! (I have more experience with Queries and Reports and not much experience with calculations in Access!)

Reply | Quote

If you let user type data directly into a table, there is no way to automatically fill one field with a calculation based on another field. As Don suggested, this requires you to use forms.

But if the second date is ALWAYS 2 years after the first, it should not be stored in the table at all. One of the general principles of Access is that you don’t store things that can be calculated..

The calculation can be performed in a query where you add the calculated field : Date_field_2 : DateAdd(“yyyy”, 2, [Date_field_1])

Anytime you want to see this second date, you look at (or have a form or report that uses) this query rather than the table.

Reply | Quote

Point well taken on not storing data that can be calculated. I will talk with the users further to see how they are going to USE that 2nd date field. I have seen one new report that needs to display that data. I can certainly modify the query or the report it is based on to do the calculation as you provided. My worry is that they will need to use it in a number of different queries and reports, which would require them to be able to understand and type in the calculation frequently when they need it. Based on the queries and reports they are already using with the data, I may be able to talk them out of having the 2nd field exist at all! If they must have the second field appear, I will most likely do the combination of an update query to update the current data, and then add something to their data entry form to ensure that NEW records will have the correct value in Date_field_2. Thanks everyone. I’ll do one more post after I meet with the user again to figure out what will work best in these circumstances.

Reply | Quote

If you create one query that contains the calculation, then you can base any further queries/reports on that first query. That way the calculated field is available, and no-one ever needs to redo the calculation.

You can base the data entry form on the query as well, so the second date will appear on the form as soon as the first date is entered.

Reply | Quote

Yes–I at least know that much, but I appreciate the idea because you could not assume I knew that about queries. What I’m worried about are all of the existing queries that the users already have that now need to include this new piece of data. And they will all be creating their own queries and reports in the future. They may base them on OTHER queries rather than this query. I really can’t stop them from doing so. That’s why I was trying to make the new date field a whole separate field in the table. The users KNOW how to add another field to their queries. And they know how to create new reports. By making the second date field a separate field in the table, the users can easily USE it in everything they do without THEM having to understand how to add the calculation to each new query or report they create in the future. Make sense?

Reply | Quote

That makes sense.

If people are still going to sometimes enter data directly into the table, you might still have some records where the second date is empty (or perhaps wrong) so you could force the update query to run periodically.

Reply | Quote

If your entering directly into the table I know of now way to auto populate the second date. Someone else will have to help you there.

To update the all the fields regardless of what is in Date_Field_2 with a year that is two years greater than the date in Date_field_1 use the following query.

UPDATE YourTableName SET YourTableName .Date_field_2 = DateAdd(“yyyy”,2,[Date_field_1]);

In your data entry form go to the design mode and select your Date_field_1 textbox. Go to its properties (right click on it and select properties)
Select the Event tab and click the After Update and select Event Procedure. Then insert the code I gave above.

Reply | Quote