Results 1 to 10 of 10
  1. #1
    oldlearner is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    31

    How do I prevent a calculated field from running until I insert the second part of the equation?


    I have a basic calculation "weight loss" from 2 other fields - ("start weight" minus "end weight"). I do not want to insert the end weight until 10 weeks after the start weight. What is the best way of preventing the calculation from executing until I insert the end weight. Any help appreciated

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can't, using a simple expression in the calculated control. But you can control what it displays in the absence of the second value. Post your expression and what you want it to show when there is no second value.
    edit:
    I presumed this was being done on a form and not in a table.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    oldlearner is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    31
    Hi Micron Thanks for your comment. I set it up in a table and placed into a form. It is a very basic calculation but I want the result to show as 0 (zero) until I input the second value. Thanks for you time.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try:

    =IIf(IsNull([End Weight]), 0, [Start Weight] - [End Weight])
    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.

  5. #5
    oldlearner is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    31
    Hi June unable to get this to work (not sure if I am putting the expression in the correct place). If I can reword the problem
    Field 1 is [start weight], Field 2 is [End weight] Field 3 [weight loss] is a calculated field [Start Weight] - [End weight]. when I insert value in the start weight it automatically drops into the result. I want the weight loss result to be 0 (zero) unless the end weight is entered.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The expression can go into the calculated control itself, or you can enter it into the control source property on the property sheet. The suggestion is what I had in mind; I was hoping to get your names without guessing, which is why I asked. However, the suggestion works exactly as typed. If you can't get it to work by placing it as suggested, post back with more explicit details about what you're doing and what the results are.
    Click image for larger version. 

Name:	CalcControl.jpg 
Views:	19 
Size:	4.0 KB 
ID:	28497
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    [Weight Loss] still shows the [Start Weight] before you enter [End Weight]? Then [End Weight] must not be null, there must be a value already there, such as empty string or 0. I assume the fields are number type so can't be an empty string. Do you have 0 as default value?
    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.

  8. #8
    oldlearner is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    31
    Thanks for your replies. You are correct June7. These are number fields and the defaults are set to 0. I have been made aware that it may not be a good idea to use the calculated field in the table so have used it in a query. This seems to achieve what I wanted to achieve but it would be good to know if it is possible to achieve in the table. Many thanks for your time.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't see why couldn't do in table. Just remove the 0 from DefaultValue property and any existing records.

    The only real negative I am aware of for use of Calculated field is backwards compatibility. I haven't incorporated in any db, only tested, but never encountered an issue with them.
    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.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only real negative I am aware of for use of Calculated field is backwards compatibility.
    Enough negatives here to convince me to not use them.
    http://allenbrowne.com/casu-14.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Calculated Field with Running Cummulative Total!
    By countingguru in forum Access
    Replies: 4
    Last Post: 06-22-2020, 08:36 AM
  2. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  3. Prevent duplicate records for calculated field
    By LoveLEE143 in forum Access
    Replies: 3
    Last Post: 05-03-2013, 11:46 AM
  4. Insert new calculated field
    By endri81 in forum Queries
    Replies: 1
    Last Post: 01-19-2013, 03:44 PM
  5. CrossTab - insert a calculated field
    By weiguo.shi in forum Queries
    Replies: 10
    Last Post: 05-23-2011, 01:20 AM

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