Results 1 to 11 of 11
  1. #1
    Velasqj1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    5

    Calculated Field Help

    I need to add a calculated field. It needs to have 5 different possibilities. So for example here are two criteria/possibilities. If FIELDa is between 0-6, then display <4.0%, if between 7-8, then display 10%. Also, in the example above, FIELDa is a number field. I need help on how to build this type of expression in the builder. Any guidance is much appreciated.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    = Iif(FIELDa >= 0 OR FIELDa <= 6,"<","") & CStr((FIELDa<0)*0 - (FIELDa >= 0)*4 - (FIELDa > 6)*6) & Iif(FIELDa >= 0 OR FIELDa <= 6,".0","") & "%"
    As you want for different numeric ranges the result formatted differently (leading "<>", trailing ".0"), you can't simply calculate returned number and use FORMAT() to return as text string with number in wanted format. You can add any number of medium steps of calculation into formula (e.g. -(FIELDa > ?)*?, where every multiplier adds a part of value to preceding result) - but steps starting with Iif() must remain in place.

  3. #3
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    I would say that the solution is as follows:
    Code:
    = IIf(FIELDa Between 0 AND 6;"<4.0%";IIf(FIELDa Between 7 AND 8;"10%";""))

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by CarlettoFed View Post
    I would say that the solution is as follows:
    Code:
    = IIf(FIELDa Between 0 AND 6,"<4.0%",IIf(FIELDa Between 7 AND 8,"10%",""))
    You may need to replace the semi-colons(;) with commas (,​)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Whether the calculation occurs in a query or in the assignment of a control's ControlSource property
    you have to use the ;
    If the calculation takes place in the code
    you have to use the ,

  6. #6
    Velasqj1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    5
    Thank you for your responses. Here is the code I came up with, however I believe there may be some errors I can’t seem to discover.

    = IIf (SOP Between 0 AND 6,"<4%",IIf (SOP Between 7 AND 8,"10%",IIf (SOP Between 9 AND 10,"15%",IIf (SOP Between 11 AND 12,"20%",IIf (SOP >12,">=33%")))))

  7. #7
    Velasqj1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    5
    I’m getting the error: Expression not supported for conversion.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You could also try Switch function. However, I've read that using such functions in queries can bog them down at least, or worse, evaluate all possibilities thus leading to unexpected results. You could also call a function that uses a Case block. That way, processing stops at the first true result. You are also close to the limit of nested IIF's I believe.

    Switch(SOP >= 0 And SOP <= 6, "<4%", SOP >= 7 And SOP <= 8, "10%", SOP >= 9 And SOP <= 10, "15%", SOP >= 11 And SOP < 12, "20%", SOP > 12, "33%")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Velasqj1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    5
    I ended up adding the calculated field to a form. I then created a query and added that same formula from the calculated field/form, to store the data. It’s working fine now. Does that sound like a good solution?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Velasqj1 View Post
    Does that sound like a good solution?
    Not in my opinion. The general consensus is that you should not store calculations unless absolutely necessary, and I can't recall ever coming across a good reason for doing so. As to whether or not the expression is a good means of showing the results in a form, that's debatable. One cannot expect every novice to know how to write a function to use in a query or form control instead, so you do what you can and learn as you go. As I mentioned, I would write a function with a Select Case block and call it from the form control or query. Which is best probably depends on the situation, but a query is usually the way to go. A form control can get the value from a query easily enough.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Velasqj1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    5
    Thanks all for your help. Mission accomplished.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-09-2019, 05:38 PM
  2. Replies: 1
    Last Post: 12-10-2017, 01:47 AM
  3. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  4. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  5. Replies: 1
    Last Post: 04-21-2013, 03:20 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