Results 1 to 10 of 10

Thread: Math Query

  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: Math Query

    I have a problem and do not know where to satrt. Guess if I would have paid attention during variables I could have done this.

    I have a member table with a birthdate column. I need to figure out the ages of the members for a population analysis.

    bdate format is mm/dd/yyyy

    any help is appreciated.

    I was thinking of using the todays date variable minus the bdate column, but im having trouble.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    datediff("yyyy",bdate, now())
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Oct 2003
    Posts
    13
    SELECT dbo_memb.acct, dbo_memb.bdate
    FROM dbo_memb
    WHERE (((dbo_memb.bdate)=DateDiff("yyyy","bdate",now())) );

    im getting a data type mismatch. any other help?

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Your getting a type mismatch because you are returning a non-date number with DateDiff and asking the SQL engine to compare it to a date.

    The DateDiff should be in your Select clause.

    What are you trying to do? There must be a reason why you want it in your Where clause.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Oct 2003
    Posts
    13
    Im not that good with my sql syntax so i wasnt sure where i should put it.

    I guess I need help formatting the select query then

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you are just looking to display the age, use this:

    SELECT acct, bdate, DateDiff("yyyy", [bdate], now()) as Age
    FROM dbo_memb

    If you wanted to lookup those who were over 25, use this:

    SELECT acct, bdate, DateDiff("yyyy", [bdate], now()) as Age
    FROM dbo_memb
    WHERE DateDiff("yyyy", [bdate], now()) > 25

    Note: I changed it slightly from yours where I put [] around bdate instead of "". I hadn't noticed it earlier in your syntax. Use quotes for literals. Bdate is a column, so use []. Well, other dogs might tell you that you don't require [] if it's a one-word column with no special characters, but it's not a bad habit to be in.

    HTH.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Oct 2003
    Posts
    13
    thanks your the man

    have a great t day

  8. #8
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation Watch out with the datediff("YYYY"...

    Watch out with the datediff("YYYY"...

    if you want to calculate the age of a person

    if someone bday = 31-Dec-2003 and the date is 1-Jan-2004 he wil be 1 year old with SQL infact hes 1 day old

    with age use :days / 365 =years
    only prob = if you have a leapyearbut this can only be 100 days off in 400 years

  9. #9
    Join Date
    Nov 2002
    Posts
    49
    I have to agree with Marvels on the complications of datediff. However, try using:

    Int(DateDiff("Y", dateofbirth, Now) / 365.25)

    With this function, you will now be right on even with leap years. How you use it from here is dependent upon what you truely need.

  10. #10
    Join Date
    Nov 2003
    Location
    Virginia Beach, VA
    Posts
    4

    Talking Re: Math Query

    Originally posted by ryancoc22
    I have a problem and do not know where to satrt. Guess if I would have paid attention during variables I could have done this.

    I have a member table with a birthdate column. I need to figure out the ages of the members for a population analysis.

    bdate format is mm/dd/yyyy

    any help is appreciated.

    I was thinking of using the todays date variable minus the bdate column, but im having trouble.
    Try using the following function I wrote. It allows many opportunities to use each type of information for different purposes beyond merely finding out someone's age. DOB in this case is the text box on a form and this code runs in the LostFocus event for that box. The result is displayed in a label on the same form.

    Function ShowAge()
    Dim iThisYear As Integer
    Dim iThisMonth As Integer
    Dim iBirthYear As Integer
    Dim iBirthmonth As Integer
    Dim iAge As Integer

    'Function Created by: R & D Data Consulting Inc.
    'Purpose: Calculate and Display the person's age in years
    'Called by: DOB_Lost_Focus
    'Calling Procedure :NONE
    'Initialize Date: 6/1/02
    'Date Modified:
    'Modifications:

    Me.lblAGE.Caption = ""
    If Not IsNull(Me.DOB) Then
    iThisYear = CInt(Year(Date))
    iThisMonth = CInt(Month(Date))
    iBirthYear = CInt(Year(Me.DOB))
    iBirthmonth = CInt(Month(Me.DOB))
    iAge = iThisYear - iBirthYear
    If iThisMonth < iBirthmonth Then iAge = (iAge - 1)
    Me.lblAGE.Caption = iAge & " Years Old"
    End If
    End Function

Posting Permissions

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