• Default value of date field (Access 2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Default value of date field (Access 2003)

    Author
    Topic
    WSlconway
    AskWoody Lounger
    December 2, 2008 at 7:21 am #455719

    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
    Viewing 0 reply threads
    Author
    Replies
    • WSDJLansing
      AskWoody Lounger
      December 2, 2008 at 7:38 am #1135662

      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
      • WSlconway
        AskWoody Lounger
        December 2, 2008 at 7:49 am #1135664

        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
        • WSDJLansing
          AskWoody Lounger
          December 2, 2008 at 7:54 am #1135666

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

          Reply | Quote
          • WSlconway
            AskWoody Lounger
            December 2, 2008 at 7:59 am #1135667

            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
            • WSjohnhutchison
              AskWoody Lounger
              December 2, 2008 at 8:27 am #1135677

              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
            • WSlconway
              AskWoody Lounger
              December 2, 2008 at 8:36 am #1135680

              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
            • WSjohnhutchison
              AskWoody Lounger
              December 2, 2008 at 8:53 am #1135684

              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
            • WSlconway
              AskWoody Lounger
              December 2, 2008 at 9:00 am #1135699

              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
            • WSjohnhutchison
              AskWoody Lounger
              December 2, 2008 at 9:10 am #1135702

              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
            • WSDJLansing
              AskWoody Lounger
              December 2, 2008 at 8:28 am #1135678

              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
    Viewing 0 reply threads
    Reply To: Default value of date field (Access 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.