Results 1 to 6 of 6
  1. #1
    majdoc is offline Novice
    Windows XP Access 2016
    Join Date
    May 2019
    Posts
    3

    Lookup and Combobox

    Hello all



    Apologies - complete newbie but struggling. I am setting up a database to assist looking after my patients. I have a list of patients in a table including date of birth and have managed to setup a query to calculate age as follows:

    Age: Year(Date())-Year([Date_of_Birth])+(Month(Date())<Month([Date_of_Birth]))+(Month(Date())=Month([Date_of_Birth]) And Day(Date())<Day([Date_of_Birth]))

    I am then trying to setup a new "age" field in the original table. When I create a new field in the table using Lookup wizard and select the results from my query, it will only add as a combobox meaning I have to select the age. Can I do this so that the result of the query (i.e. the age) is inputted directly into the new "age" field in my original table.

    Understand this is very basic stuff but have been at this for ages now!
    Majdoc

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am then trying to setup a new "age" field in the original table.
    You should not store such calculated values in any table. In a form textbox, show age as = Date - DOB (or whatever you call it) assuming that the value in the table field is the entire date and not just a portion of it. How you format the control depends on what you want to see. If only whole numbers and you don't care about rounding, then Integer values should work. You can try a ready made format on the property sheet for the textbox.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can simplify your expression too

    iif(format([Date1], "mmdd") >= format([Date2], "mmdd"), 0, -1) + datediff("yyyy", [Date1], [Date2])

    you'll have to play around with the 1 or 0 to get the age correct

  4. #4
    majdoc is offline Novice
    Windows XP Access 2016
    Join Date
    May 2019
    Posts
    3
    Hi

    The expression works fine and gives the expected answer (i's important the age is correct in years). The problem I am having is I can't use that result of that query to populate a new field. Any ideas?

    Majdoc

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The advice from Micron about storing calculated age in your table is valid. A person's age changes daily. If you store age in a table, in one year that age is guaranteed to be wrong. Queries will calculate age on the fly in real time and are accurate in real time.
    An exception could be if you want to store the age at which a diagnosis or procedure occurred. Even then, if the date of the diagnosis is known, the age could still be calculated by the query for that date.

    Also, if you are trying to do your data entry directly into tables, you will have trouble doing what you want to do. All data entry should be done via forms.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes, davegri has it right. I apologize if my answer wasn't clear, I was just giving you an easier formula to resolve the age on any reporting/form you needed rather than how to store it in a table, which, as noted by others, is a bad idea.

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

Similar Threads

  1. Replies: 13
    Last Post: 06-28-2018, 06:47 PM
  2. Code setting content of Field with ComboBox lookup
    By coffee4kepi in forum Modules
    Replies: 3
    Last Post: 02-02-2018, 04:46 AM
  3. Replies: 7
    Last Post: 03-02-2016, 09:17 PM
  4. Combobox record lookup and .oldvalue compatibility issue.
    By MatthewGrace in forum Programming
    Replies: 3
    Last Post: 08-02-2014, 10:23 PM
  5. Replies: 1
    Last Post: 06-24-2013, 05:14 PM

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