Results 1 to 6 of 6
  1. #1
    KNap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    28

    Query Issue

    Hi

    I have written a database, and have designed a form based on a query - it calculates age at registration, actual age and the number of years a client has been known to a service. However, when opening the query or form I am now being asked for the registration age etc....

    The query code is this - can someone tell me if it looks right please?

    Registration Age: ([Date of Registration]-[Date of Birth])/362.25


    Age: Year(Now())-Year([Date of Birth])
    Years known to Services: ([Registration Age]-[Age])*-1

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,550
    The query will ask you if:

    If you don't spell the field correctly,
    if the field does not exist.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,880
    How accurate do you want to be? By your formula I am a year older than my actual age because my birthday has not happened this year yet.

    to get an accurate age use a function

    Code:
    Public Function AgeYears(ByVal datBirthDate As Date) As Integer
      ' Comments: Returns the age in years
      ' Params  : datBirthDate    Date to check
      ' Returns : Number of years
      ' Source  : Total Visual SourceBook
     
      On Error GoTo Proc_Err
     
      Dim intYears As Integer
     
      intYears = Year(Now) - Year(datBirthDate)
     
      If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
       ' Subtract a year if birthday hasn't arrived this year
        intYears = intYears - 1
      End If
     
      AgeYears = intYears
     
    Proc_Exit:
      Exit Function
     
    Proc_Err:
      MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
      Resume Proc_Exit
    End Function
    To get the age at registration in years, assuming registration occurs after birth, use
    Code:
    datediff("yyyy",Birthdate,RegDate)
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,870
    In addition to the advice given already, Access does not necessarily play well with names that include embedded spaces. If you are in design mode, then I suggest you adjust your naming convention to avoid spaces within field and object names.

    Why exactly do you need Age with decimal places?
    Last edited by orange; 03-17-2021 at 10:24 AM.

  5. #5
    Micron is online now Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,425
    Is there some type of year that only has 362.25 days? I've heard of Days360...
    I agree with Ranman - a field, alias or some reference is likely misspelled.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,880
    Normally I use the above function for age because its in a module I usually drag from project to project and readily available.

    I believe this expression also gives a current age

    Code:
    Int(datediff("d",DateOfBirth,date())/365.2421 )
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Query issue
    By RayMilhon in forum Queries
    Replies: 2
    Last Post: 03-29-2017, 03:35 PM
  2. Replies: 2
    Last Post: 07-08-2016, 08:01 AM
  3. Replies: 4
    Last Post: 11-14-2015, 03:23 AM
  4. Replies: 5
    Last Post: 01-08-2015, 02:08 PM
  5. Query issue
    By BRZ-Ryan in forum Queries
    Replies: 12
    Last Post: 01-14-2014, 03:23 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
  •  
Other Forums: Microsoft Office Forums