Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35

    Question Calculated Field help

    Hello,



    I have a field (field3) that performs a division calculation (divide field1 into field2 and show percentage in field3) however if the values in field1 and field2 are zero I get #NUM! in the field in the table.

    Below is the expression I have for that field... how can I modify it to make the field just display 0% in this event?

    [ Field1 ]/[ Field2 ] (I added spaces so it wouldn't try to parse it as BBCode)

    Thank you so much in advance for your help.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should do calculations in a Query and not a Field in a table. Having said that you can use the IIF() function and test the divisor for 0.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree with RG, don't do calculations on your tables, don't use multi value fields on your tables either, they will lead to no end of grief. Instead rely on calculations in queries or as a field on a form or report that is unbound (not tied to a data source)

  4. #4
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Quote Originally Posted by RuralGuy View Post
    You should do calculations in a Query and not a Field in a table. Having said that you can use the IIF() function and test the divisor for 0.
    I understand completely regarding the query but this is just a simple field that I pull the information from for simple reports

    I tried this:

    =IIf([Total Vis Good] And [Total Vis Inspected]= 0,"0",[Total Vis Good]/[Total Vis Inspected])

    But it tells me "The expression could not be saved because of it's result type, such as binary or NULL, is not supported by the server"

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There is no issue dividing INTO 0, only when dividing BY 0.
    =IIF([Total Vis Inspected] = 0, 0,[Total Vis Good]/[Total Vis Inspected])
    ...should do it.
    Another note, not advisable to have object names with embedded spaces. I use CamelFontName myself.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably because you have set the true part to be text and the false part to be a number. Also your statement is not making sense - if both [Total vis Good] and [total vis inspected]=0 then that will return a false value so it will try to divide by zero. Should the AND be an OR?

  7. #7
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Quote Originally Posted by RuralGuy View Post
    There is no issue dividing INTO 0, only when dividing BY 0.
    =IIF([Total Vis Inspected] = 0, 0,[Total Vis Good]/[Total Vis Inspected])
    ...should do it.
    Another note, not advisable to have object names with embedded spaces. I use CamelFontName myself.
    Thank you for the tip about the imbedded spaces.... however when I try your expression and I click "OK" the expression window just flashes and stays up as if it doesn't like the expression but displays no error

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Click "OK" where? What else are you doing?

  9. #9
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Quote Originally Posted by RuralGuy View Post
    Click "OK" where? What else are you doing?
    Ok... I am right-clicking on the field name in the table and choosing to modify the expression. The window that pops up contains the expression for the calculation for that field... I put that expression you gave me in that window and click "OK" just as I did when I originally put the expression to make one divide by the other in the original post

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I think you lost me there. You have the table open is Datasheet view?

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    ...however when I try your expression and I click "OK" the expression window just flashes and stays up as if it doesn't like the expression but displays no error
    I think it may be because Iif() is not allowed in the expression for a calculated in a table. The complexity of expressions you can use is extremely limited.

    If you really want to keep that field in the table, then change the data type to double, and do the calculations in a form or in an Update query.
    Last edited by John_G; 08-18-2016 at 12:56 PM. Reason: spelling

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I understand completely regarding the query but this is just a simple field that I pull the information from for simple reports
    For all the reasons mentioned above, you should really do it in a Query.
    Note that Reports can use Queries as their Record Source just as easily as they can use Tables.
    So there really is no reason to avoid using a Query here.

  13. #13
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Quote Originally Posted by RuralGuy View Post
    I think you lost me there. You have the table open is Datasheet view?
    Table open in data sheet view


    Click image for larger version. 

Name:	Capture1.JPG 
Views:	30 
Size:	20.4 KB 
ID:	25504


    Right-click on field name and choose "modify expression" and this window pops up:


    Click image for larger version. 

Name:	Capture2.JPG 
Views:	30 
Size:	97.1 KB 
ID:	25505


    Change expression and click "OK" (circled in red)


    Click image for larger version. 

Name:	Capture3.JPG 
Views:	30 
Size:	47.0 KB 
ID:	25506

    Dialog box just flashes but no error and I have to cancel to make it close

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hmm... I think both Joe and John have hit the nail on the head. Why not do this in a Query instead? What I gave you *will* work in a query.

  15. #15
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    I don't know how to do that. I am using a querry to pull the data from the fields and using a making a report that uses that query but I don't know how to do what you are saying.

    Please don't tell me to go read

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-06-2016, 02:52 PM
  2. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  3. Replies: 2
    Last Post: 12-15-2013, 02:29 AM
  4. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 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