Results 1 to 7 of 7
  1. #1
    guyjj is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    1

    Problem adding Calculated field to a table

    I have a database of electronic components. For capacitors the software using the data wants a Real number for the value so as an example a 1uF capacitor needs to be entered as 0.0000001 and a pF as 0.000000000001.
    I set up the form with a Value field, Farad field and a ValueR field.
    the form is filled as (example):
    Value |Farad | ValueR |
    | .1 ..| ..u ..| ............|
    I added a calculated field "ValueRr" in a form: =IIf([Farad]="u","0.0000001"*[Value],IIf([Farad]="p","0.000000000001"*[Value]))
    Then added a button to copy the result to the ValueR Field: Forms!cap_smd.ValueR.Value = Forms!cap_smd.ValueRr.Value.


    This works if the ValueR field is a text field but the software needs to see it as a number. If I change it to a number type, the ValueR field displays "0".
    How can I get this to work or is there a better way to solve this? I couldn't find a way to have the result put directly into the ValueR field

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The usual mantra is that you shouldn't be storing calculations in table fields, you perform the calculations on the fly via queries or forms. In table design view did you try setting the field data type to number>decimal and choose something other than 0 for the scale property?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You have put quote marks around your numbers (which means they're now strings) which to be honest I'm amazed the calculation works.
    Remove the quotes, and you should get a number as a result.

    Unless the Value field is ever going to be updated a simple update query would store all the values in one go.

    Personally I would probably store the value in UFarads rather than the Farad expression and it's value.
    It's the most common value and easy to present.
    Then your actual number, if required, is a simple static multiplication.

    ValueUFarads Multiplier Real Value
    10000 .0000001 .01
    100 .0000001 .0001
    1 .0000001 .0000001
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    In case you want to keep the calculated value in table, add a disabled, locked or hidden control linked to field ValueR, and write AfterUpdate events for controls linked to fields Value and Farad (or BeforeUpdate event for form), which are writing a new value into this disabled/locked/hidden control (NB! Into control, not into field!) whenever the newly calculated and old values differ. Then whenever the user leaves the record, and any value in any control of form was changed (this disabled/locked/hidden control included), the record will be updated. When user cancels record updates (e.g. pressing Esc), the the record is not updated (included field linked to ValueR). In case you update this field writing the new value into table instead of control, there are possible situations, where this field doesn't match with values in fields Value and Farad anymore.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Minty View Post
    You have put quote marks around your numbers (which means they're now strings
    OP used quotation marks because they are text to begin with?
    This works if the ValueR field is a text field but the software needs to see it as a number. If I change it to a number type, the ValueR field displays "0"
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Micron View Post
    OP used quotation marks because they are text to begin with?
    What I meant was assuming [Value] IS a number multiplying it by "0.00000001" probably shouldn't work. Which may indicate that the [Value] field is also text??
    Shirley the [Value] field should be a number as should the ValueR

    And while I'm here Value is a reserved word and shouldn't be used for a field name.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Like that guy on the airplane said "...and don't call me Shirley".

    IIRC if Access can interpret strings as numbers, it will perform calculations with them.
    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. Replies: 6
    Last Post: 09-03-2020, 03:27 PM
  2. Adding Calculated Field to Table with VBA
    By virgilio in forum Programming
    Replies: 8
    Last Post: 08-28-2020, 04:40 PM
  3. Replies: 16
    Last Post: 02-23-2019, 06:17 AM
  4. Adding a calculated field to report
    By Danr94 in forum Forms
    Replies: 6
    Last Post: 01-24-2018, 11:47 AM
  5. Replies: 1
    Last Post: 11-18-2014, 02:16 PM

Tags for this Thread

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