Results 1 to 11 of 11
  1. #1
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    33

    Calculated Field Result Wrong

    Hi,



    I have a query with a calculated field that simply divides one field by another. The problem is the results are wrong with the decimal point being in the wrong place.

    Eg the first record is 42/722 which should give 0.060941828254848. Instead I get 6.0941828254848.

    The SQL is just FieldA/FieldB AS ABFactor.

    Can anyone think what the issue might be?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,791
    strange.
    try converting both to the same data type in the math:

    =cSng(FieldA) / cSng(FieldB)

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    Your field is formatted as %?
    However, I can't even get what you say is supposed to be the answer. Calculator shows 0.0581717451523546 not 0.060941828254848
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    616
    I'd guess a % format as well?

  5. #5
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    33
    Thanks for your responses guys.

    Ranman, I tried that but still get the same.

    Micron, you're right, apologies there was a typo in the formula. It is actually 44/722.

    I've checked the field and it isn't formatted as a %. I tried formatting as a General number and got the same result but formatting as a Standard number works.

    It seems very odd but the workaround should be ok.

    I import the query into Excel for analysis so hopefully it won't cause issues.

    Thanks again.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,229
    Try qyery
    SELECT CDbl(FieldA) As FieldA, CDbl(FieldB) As FieldB FROM YourTable WHERE ...
    The query returns actual numeric values for both fields. Are they 44 and 772 for this row as you assumed?
    Last edited by ArviLaanemets; 11-26-2020 at 07:55 AM.

  7. #7
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    616
    Are the other fields formatted as %?, particularly the field with the value of 722 ?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    The format (if that is what did it) could be in the table or the query itself. Don't know which you're checking - or if both. It seemed the most likely culprit to me seeing as how 0.060 is 6%. One of the input fields being percent makes some sense too.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,925
    think you are not seeing the bit at the end in red

    ?44/722
    6.09418282548476E-02

    which says 'move the dp two places to the left' i.e. divide by 10^2

    you can use the eval function

    ?eval(44/722)
    0.0609418282548476

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    think you are not seeing the bit at the end in red
    I think we have a winner!!
    That's probably better than setting the query field type to Fixed or Standard because with those, you'd have to specify number of decimal places.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  11. #11
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    33
    Thanks Ajax, that seems to have fixed it.

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

Similar Threads

  1. Replies: 4
    Last Post: 07-28-2019, 06:56 PM
  2. Wrong result
    By Alex Motilal in forum Queries
    Replies: 5
    Last Post: 12-19-2017, 04:12 AM
  3. Replies: 5
    Last Post: 04-13-2016, 05:21 PM
  4. Replies: 2
    Last Post: 04-14-2015, 08:28 AM
  5. Replies: 1
    Last Post: 01-09-2013, 02:42 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 - Senior Forums