Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Location
    GA
    Posts
    4

    Unanswered: working with dates

    Sorry this may run long.

    I am createing a Database for my Platoon. I would like to set up a form where I can enter a birthdate and have another field display an age.

    I have done this before at a previous duty station, but cannot for the life of me remember how I did it.

    I have tried going into the properties area for the field and entering an expression that will subtract the birthday from the current date then devide the answer by 365, but cannot get an answer to display in the age field.

    I have also tried to type in a Datediff expression in the age text box. that makes the age box display something like #name?.

    Any help anyone here could give would be greatly appreciated.

    thank you very much,
    Valearl

  2. #2
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Valearl,

    In the after update event of the birthday field, type the following code

    [age field name]=DateDiff("y", me![birthday field name], Date)

    the function DateDiff with the parameter "y", executes the diference in years from the birth date to today's date.


    For more information, see the help for DateDiff



    hope this helps!

  3. #3
    Join Date
    May 2002
    Location
    GA
    Posts
    4
    Thanks for the reply Miguel.

    I tried what you said and will go over it here just to let you see what I did and if I may have missed a step somewhere.

    I opened the form in design view. Opened the field for the birthdate (child 1 DOB). Opened the properities window to the event tab. Typed in your formula. Then closed that window down, and opened the form in form view. I tried putting a birthdate in on that form and cycled through the rest of the form till I hit a new form and tried inputing a birthday there also.

    I did not get anything to come up in the age field at all.

    The field names are Child 1 Age and Child 1 DOB. I also have fields for up to child 4, but I know once I get it to work on the first the rest are easy only changeing the number for the rest of the blocks.

    Again thank you for your sugestion, and if you can see where I messed it up please point it out to me I will try again.

    Thanks
    Valearl

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey Valearl,

    Try these steps:

    Right click on you field named Child 1 DOB and choose properties, then choose the event tab and find the After_Update field. Click on the ... button on the far right and then choose Code Builder. Type the following code:

    Private Sub child_1_dob_AfterUpdate()
    Me.[child 1 age] = Round(DateDiff("d", Me.[child 1 dob], Date) / 365, 0)
    End Sub

    This will use the days divided by 365 to calculate the years. Or, you can use miguels theory and change the "d" to "yyyy" minus the Round function. Also, you really should take the spaces out of your field names. They can become a real pain when your programming. You could do something like Child1Age or Child_1_Age.

    Hope this helps
    Kal

  5. #5
    Join Date
    May 2002
    Location
    GA
    Posts
    4

    Thank you Kpalmer

    I started with your code and had a couple problems at first but finally ended up with this:

    Child_1_DOB_AfterUpdate()
    Me.[Child 1 Age] = Datediff("yyyy",Me.[child 1 DOB],date)
    End Sub

    thank you so much for the help that is the first time I have messed with the code builder.

    Thankd Again,
    Valearl

  6. #6
    Join Date
    May 2002
    Location
    GA
    Posts
    4
    Dont know how far this thread was bummped to the bottom of the message board so here is my "bump"

    As I stated I enteted the Code as Kpalmer gave me and I thought it was working correctly. The problem is:

    If a birthdate is in Dec 00 and todays date is used to make the calculation the code will put the age as 2 although the person is only 1 year old and will not be for another 6 months.

    Any help with this would be greatly appreciated.

    Thank you
    Valearl

  7. #7
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey Valearl,

    In the example I showed you the code is calculating by days. You really shouldn't calculate by years as you have now found out that years are not accurate. The code I placed here should work fine what kind of error message are you getting?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •