Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Dob Need Splitted In Years, Month & Days

    Hi,

    I am developing a db for a small clinic to register name of patients. One of the forms called "F_PatientMaster" has following some text box fields on it. But my question here relates with only 4 text boxes:

    P_DOB (Datefield)
    P_AgeYear (Number)
    P_AgeMonths (Number)
    P_AgeDays (Number)

    While getting addmitted for treatment at the receiption counter patient describes there age in years like this patient is 40 years old, another is 27 year old etc. Sometime infants / newly born babies are the patients and sometime their parents know the exact date of birh that is being entered into system but sometimes parent just describes their age in days or months.

    And last three fields are compulsory for input where my user need that once he enter in year box let us 3 (3 years), so Date of Birth should be calculated automatically less 3 years from the current date i.e. if today is considered 01/01/09 it should be auto entered in P_DOB text box 01/01/06. In the same way, if entered 3 in P_AgeMonths field (3 month) then should be auto entered in P_DOB text box 01/10/09 and finally for infants if value entered in P_AgeDays textbox (let us say 5) so P_DOB should automatically be 26/12/08.

    It also should produce the same result if date is entered in P_DOB directly.

    Example if entered 01/06/85 then in rest of the 3 text boxes values should be:
    23 years 9 months 21 Days in respective textboxes.

    I have did following way but not satisfied with logic...let me know if someone have good idea over it.

    Private Sub P_Dob_AfterUpdate()
    Me.P_AgeYear = DateDiff("Y", [P_Dob], Now()) / 365
    End Sub


    This produces only year while I need months and days too.

    Private Sub P_AgeYear_AfterUpdate()
    P_Dob = Date
    Dim Yr As Integer
    Dim mth As Integer
    Dim Dys As Integer
    Yr = Year(Date)
    mth = P_AgeYear * 12
    Dys = mth * 30
    P_Dob = P_Dob - Dys
    End Sub



    This gives reduced month only

    Private Sub P_AgeMonth_AfterUpdate()
    If P_AgeMonth > 11 Then
    Me.P_AgeMonth = Null
    MsgBox "PATIENT IS MORE THAN A YEAR OLD"
    Me.P_AgeYear.SetFocus
    Else
    P_Dob = Date
    Dim Mtn As Integer
    Dim Dys As Integer
    Dys = (P_AgeMonth * 30)
    P_Dob = (P_Dob - Dys)
    End If
    End Sub


    This is on because these are days less than 30

    Private Sub P_AgeDays_AfterUpdate()
    P_Dob = Date - P_AgeDays
    If P_AgeDays > 30 Then
    Me.P_AgeDays = Null
    MsgBox "PATIENT IS MORE THAN A MONTH OLD"
    Me.P_AgeMonth.SetFocus
    End If
    End Sub
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Ashfaque

    You understand this would not normally be considered good practice? This is a problem as you have dependencies between non-key attributes. By tomorrow, all the age data you entered today will be wrong.

    There is nothing really wrong with estimating a DOB from an age, but then also storing the age would be considered a design error.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, your algorithm for calculating age is not accurate enough. As Rudy has pointed out before, this is a rather long winded but it is accurate:
    Access/VBA Tutorials - Calculate a persons age given the DOB
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I think you have over kill it

    here is function that works out the Age of someone down to the Months

    Code:
    Function Age(Birth_Date, End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    'Check if no error
    If IsNull(Birth_Date) Or Birth_Date = "" Then
        Age = 0#
    Else
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 Then Years = ""
        Age = Years & "." & DIG(Months - Temp)
    End If
    
    End Function
    
    Function DIG(num)
    If Len(num) < 2 Then
    DIG = "0" & num
    Else
    DIG = num
    End If
    End Function
    just copy the code into a module

    them you can just call

    MyAge = age([P_Dob],now())

    what i do is put a text box on the screen in the control source =age([P_Dob],now()) then make the text look like a label enable = false tabstop = false
    then what happen every time you look at that record the age will show right.



    in a query just do MyAge:age([P_Dob],now())



    if [p_dob] = 24/01/65

    myage = 44.02

    which is 44 years old and 2 months

    as we divide it by 12 we will only see
    .00 to .11

    question
    why do you need to know the age in Days

    you could change the function to work out the Days divide it by 365.4
    but problem is each month has difference day count so the your algorithm will have work out
    what month DOB is how many days in that months after that to this months and day number of this month
    yes it could be done but what the value of you time to the value of the data showing in days.

    When I use the age function it funny to watch the user work out and count the mouths to see if it wrong
    I have seen user go back and look at records checking to see if it still right.
    Last edited by myle; 03-22-09 at 18:21.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks pootle, Myle for your valuable responses over the issue.

    Pootle, I know the value (age) will be changed every next day. But my plan was like this. Once any transaction takes place for the patients, the calculation should be restored. Like for example on a search form if user selects particular patient (on the same form), it code will re-calculate the age and shift the value in the respected boxes. So the master records will be updated automatically. I think we even can apply this while previewing / printing reports.

    Let me try myle's idea and let you people know if I could be success.

    With kind regards,
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Ashfaque
    Pootle, I know the value (age) will be changed every next day. But my plan was like this. Once any transaction takes place for the patients, the calculation should be restored. Like for example on a search form if user selects particular patient (on the same form), it code will re-calculate the age and shift the value in the respected boxes. So the master records will be updated automatically. I think we even can apply this while previewing / printing reports.
    This is called a Third Normal Form violation and is a fundamental design error. You should not store age - store only the date of birth. Calculate age when you need to display it only, don't store it anywhere.

    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks again Pootle,

    I will do that.....
    With kind regards,
    Ashfaque

  8. #8
    Join Date
    May 2009
    Location
    Poland
    Posts
    2

    Hi, Is there anybody knows how to teach baby from early pregnancy?

    Hi Parents,

    I want to know, is there any ways to start teaching baby from pregnancy?
    Like how we talk to him/her or anything..
    Cause i want to make my baby the best..

    Thanks
    Last edited by pootle flump; 06-01-09 at 05:32.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What in the?

    Yes, just go to Access, select Tools -► Teach Fertilised Cell -► Advanced Physics.

    lol

    Of all the places to ask ^^

    I hate to burst your bubble, but in early pregnancy, there is no BABY. It's called a fertilised cell.... then a foetus. It's only a "baby" in LATE pregnancy... some would argue that birth has to occur first. I also don't believe you can teach an unborn child anything. Just play it nice music perhaps.

    I know you are a proud expecting father and all, I know, I've been there... but I think it might be time for you to get your feet back on Earth. Your child will need you to be well grounded to give them the best start you can.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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