Results 1 to 8 of 8

Calculate one field based on another one

  1. #1
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    90

    Question Calculate one field based on another one

    Hi,

    I have a form on Access with 2 fields. Date1 and Date2. Usually Date2 is 1 year ahead Date1. So I would like an expression that when I type Date1, Date2 would be automatically filled with Date1 + 1 Year.
    But that is usually. Not always.

    In the field Date2, properties, Event and On Get Focus, I put the following expression:

    Code:
    =DateSerial((Year([Date1])+1);Month([Date1]);Day([Date1]))
    Iīm sure the expression is working correctly because if I put it on DefaultValue or Control Source, it works perfectly. The problem is that if I put on DefaultValue the expression triggers when I open the form, and when I open the form the field Date1 is empty. And if I put on Control Source I canīt change the value.

    I also already tried to put it on On Enter, On Click, everything. It doesnīt work.
    The format is DD/MM/YYYY.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,960
    Set the Date2 control in the AfterUpdate event of the Date1 control.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    90

    Question

    Quote Originally Posted by RuralGuy View Post
    Set the Date2 control in the AfterUpdate event of the Date1 control.
    I donīt understand you.

    I did the following:
    In Date1, AfterUpdate I put = [Date2].ControlSource

    Is that it? Doesnt work.

    Also tried to put the entire code expression in AfterUpdate of Date1. Doesnt work.

    Also tried this, doesnt work:

    AfterUpdate of Date1 - Event Procedure:

    Code:
    Private Sub Date1_AfterUpdate()
      
      Me!Date2 = DateAdd("yyyy", 1, Me!Date2)
        
    End Sub

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,960
    The code should be:
    Code:
    Private Sub Date1_AfterUpdate()
      
      Me!Date2 = DateAdd("yyyy", 1, Me!Date1)
        
    End Sub
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    90
    Quote Originally Posted by RuralGuy View Post
    The code should be:
    Code:
    Private Sub Date1_AfterUpdate()
      
      Me!Date2 = DateAdd("yyyy", 1, Me!Date1)
        
    End Sub
    Still doesnīt work. I get no error, no message. Is like nothing changed.
    I build a brand new database, only with these two field to test in an enviroment free of other variables and still nothing.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,960
    Are both controls bound to DateTime fields? Try this and tell me what happens:
    Code:
    Private Sub Date1_AfterUpdate()
        Me!Date2 = DateAdd("yyyy", 1, Me!Date1)
        MsgBox "[" & DateAdd("yyyy", 1, Me!Date1) & "]"
    End Sub
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    90

    Thumbs up

    Quote Originally Posted by RuralGuy View Post
    Are both controls bound to DateTime fields? Try this and tell me what happens:
    Code:
    Private Sub Date1_AfterUpdate()
        Me!Date2 = DateAdd("yyyy", 1, Me!Date1)
        MsgBox "[" & DateAdd("yyyy", 1, Me!Date1) & "]"
    End Sub
    Now it WORKS!
    I dont know what was hapening. I took out the dialog box and still works.

    Thank you!

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,960
    Great! Are you ready to mark this thread as Solved? It is a thread tool.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selecting a corresponding table field based on text field.
    By michaeljohnh in forum Programming
    Replies: 5
    Last Post: 10-08-2010, 09:33 AM
  2. Replies: 2
    Last Post: 09-20-2010, 08:02 PM
  3. Autofill field based on autonumber field
    By frevilla in forum Forms
    Replies: 1
    Last Post: 09-11-2009, 01:50 AM
  4. Total based on Formula based on field value
    By cjbuechler in forum Reports
    Replies: 15
    Last Post: 07-10-2009, 08:56 AM
  5. Replies: 0
    Last Post: 02-15-2009, 07:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums