Results 1 to 6 of 6
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Calculated field error when in a sub-form

    I have two calculated fields in a query, one shows the age in years, the other the remaining months e.g. 7 years & 8 months. The calculation works perfectly in the query and also when I build a form based on the query, however If I then make that form a sub-form inside another form it errors. I have made no changes to the form or query at all. Could anyone suggest why this might be and how to fix it? Thanks

    Opened as a sub-form within another form

    Same form but open independentlyClick image for larger version. 

Name:	Capture1.JPG 
Views:	11 
Size:	11.5 KB 
ID:	46657Click image for larger version. 

Name:	Capture2.JPG 
Views:	11 
Size:	12.8 KB 
ID:	46658

  2. #2
    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,716

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    "Age: Int((Date()-[Date of Birth])/365.2425)
    Months: Int((DateDiff("d",[Date of Birth],Date())/30.41667)-([Age]*12))"

  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,716
    How are form and subform related? Can you post a copy of database or show form/subform and related code?
    What is the error message/number? What have you tried to resolve things?

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    The form is a tabbed form (8 tabs) with many other sub-forms in it all with various information about the service user and all linked by service user ID (no other issues). The error just shows #Error (Not sure how to find an associated number for it) I have tried, repaint, refresh and require on the on open properties of the form. I have sorted a work around of putting an "Age" button next to the DOB field that when clicked opens a pop up independent form just showing Name, DOB, Age in years and months. This works fine and is based on the same query but less convenient for the user as the have to click the button to open the pop up view the info then close, rather than just see the information on screen. Sorry to large to send copy of DB but if its not a common obvious problem then I will live with my work around. Thanks

  6. #6
    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,716
    Have you tried compact/repair then zipping the database for posting?

    Also, is your month calculation correct? Date == Nov 2021 (11th month) DateofBirth ==Feb 2014 (2nd month) 11-2 = 9 ??

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

Similar Threads

  1. Replies: 3
    Last Post: 06-24-2017, 06:39 PM
  2. Calculated field #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 03-30-2014, 11:35 AM
  3. Replies: 3
    Last Post: 05-26-2013, 08:57 AM
  4. Math error in calculated field
    By nashgas in forum Queries
    Replies: 2
    Last Post: 04-03-2012, 11:49 AM
  5. Replies: 5
    Last Post: 09-17-2010, 09:48 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