Results 1 to 5 of 5
  1. #1
    Tazmaniac is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    19

    Calculated fields in queries

    Hi all

    I have created a query in order to work out a persons age based on todays date and his dob. How do I get the calculated field to display in an already existing form?

    I would also like the calculation to store in the forms table.



    Thank you in advance!

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Storing calculated values in the table is a big no no. http://allenbrowne.com/casu-14.html

    There are a couple ways to add this to a form. The easiest would probably be to have your form use your query as its recordsource then simply add a textbox to the form with your calculation field as its control source.

    If you only need it for display purposes you could put your age calculation formula in an unbound textbox on your form and not have to mess with the form's current recordsource.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Saving calculated data is usually a bad design. Calculate when needed. Requires code to save calculated data.

    If the form is bound to the query then add a textbox to form and bind it to the field.

    If bound to table then use an expression in textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Tazmaniac is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    19
    Thank you for the reply.

    Yes, I would be happy to at least have the age calculated field from the query to be displayed on the form.
    I have inserted a text box on my form, how do I go about displaying the value from the query in the text box?

    I am not clued up on the programming side of things yet :-(

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How precise do you want the age to be calculated? Consider: DateDiff("YYYY", "your field here", Date())

    Also review http://www.fontstuff.com/access/acctut05.htm

    and

    https://support.microsoft.com/en-us/...nd-in-years-in


    Expression can be in query or in textbox. Expressions in textbox must be preceded with equal sign.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2017, 07:39 AM
  2. Replies: 2
    Last Post: 08-13-2015, 12:58 PM
  3. Replies: 2
    Last Post: 11-26-2013, 03:29 PM
  4. Replies: 12
    Last Post: 10-01-2013, 12:59 PM
  5. Iif and calculated queries problem
    By erringtonl in forum Queries
    Replies: 1
    Last Post: 01-20-2012, 02:20 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