Results 1 to 4 of 4
  1. #1
    Jerry Call is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Champaign, Illinois
    Posts
    85

    Can an expression be used to set the default value of a table field in design mode?

    I think the answer to my question may be yes, but maybe not. At least at present I've been unsuccessful in constructing one.


    Throws an error.

    Here's what I want the default value of the field ItemValue (currency) to be:

    Divide the field FullUnitCost (currency) by the field NoInFull (numeric), then multiply the result by the field CumTally (numeric)
    and round the result to 3 decimal places.

    So the expression would be--

    =Round([FullUnitCost]/[NoInFull]*[CumTally],3)

    Now if I put this expression into an unbound field on a report form based on the table in question, it works.

    But I was thinking it might be smoother if this calculation could just be built into the table design itself.

    If this isn't possible, I'm fine with doing the calcs in the report, but I just wondered if this could be done
    in the table design. No problem, of course, if one is going to use something like "United States" or "John".
    But Access doesn't seem to like an expression like the above in the Default Value field of a table design.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I would not use the expression in a field in the table even if it is possible.
    As you already know, it works on a form/report and but I would use it as a calculated field in the query that would be used for the form/report or anywhere else in the app where the calculation is needed.
    Not usually recommended to keep the result of a calculation in a table field but rather do the calculation whenever and wherever it is required.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  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
    Can have a Calculated type field in table. However, I never use this field type.
    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
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    No matter where you put it, it seems to me the written description of your mathematics does not match the formula. You need additional parenthesis to make the formula match the sentence.

    "Divide the field FullUnitCost (currency) by the field NoInFull (numeric), then multiply the result by the field CumTally . . . "

    =Round(([FullUnitCost]/[NoInFull])*[CumTally],3)

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

Similar Threads

  1. Text Box Default Value (Form in Edit Mode)
    By RiskIt in forum Access
    Replies: 9
    Last Post: 03-28-2019, 05:20 AM
  2. Replies: 3
    Last Post: 09-21-2017, 07:02 AM
  3. Replies: 5
    Last Post: 05-24-2016, 06:08 AM
  4. Replies: 6
    Last Post: 03-16-2016, 08:36 AM
  5. Replies: 4
    Last Post: 01-14-2011, 10:37 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