Results 1 to 12 of 12
  1. #1
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30

    Can Someone help me with this one please !!! Banging Head On Keyboard

    Greetings everyone.

    I have a form with a couple of text boxes bound with the following

    =IIf([TEST1_Compaction]=[Project_Spec]+[Spec_Allowence_Compaction] Or [TEST1_Compaction]=[Project_Spec]-[Spec_Allowence_Compaction],"Pass","Fail")

    what needs to happen is if the TEST1_compaction field equals the project spec within the range per say +/- 3% then it needs to say pass otherwise fail.

    i am pretty sure that the above is saying either 3 above or 3 below but not the range in between. Am i correct or does someone know what it needs to say to pass as long as Test1 COmpaction is within the range

    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	22.5 KB 
ID:	46234

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    910
    =IIf(abs([Project_Spec] - [TEST1_Compaction]) <= [Spec_Allowence_Compaction], "pass", "fail")

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    I could be wrong but I'd say that solution will only provide one value, not a range.
    maybe
    =IIf([TEST1_Compaction]<=[Project_Spec]+[Spec_Allowence_Compaction] AND [TEST1_Compaction]>=[Project_Spec]-[Spec_Allowence_Compaction],"Pass","Fail") ?

    Hard for me to say because I see no form field called Project_Spec but if I use Compaction_Spec I get a range of 95.2 to 100.8 which is a fail in this case.
    - "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
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    910
    Quote Originally Posted by Micron View Post
    I could be wrong but I'd say that solution will only provide one value, not a range.
    It compares the absolute value of the delta to the allowance.

    It works in my test. The thing to be weary of us floating point rounding errors. One way to get around that would be to round both sides of the equation like this
    Code:
    =IIf(Round(Abs([Project_Spec]-[TEST1_Compaction]),6)<=Round([Spec_Allowence_Compaction],6),"pass","fail")
    I rounded (round, rounded?? I no speak gud) to 6 decimal places, I don't know what kind of precision is required.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,713
    It compares the absolute value of the delta to the allowance.
    I could see that you were using the absolute value but it didn't sink in before coffee. I have remedied that now but think I should have caught on right away because I swear I've used that before. It works because the high and low is the same deviation value.
    Nice one!
    - "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.

  6. #6
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30
    thank you all for the responses

    I think the AND is a definite need in this case because both conditions must be true to be a pass so if i understand the language correctly if say the project spec is between 93 and 97 it should say pass correct? im not sure whether KD2017 response would compare both but i am not that good at understanding. I know what i want it to do lol just not sure how to make it do it.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    910
    Quote Originally Posted by rshaw63 View Post
    thank you all for the responses

    I think the AND is a definite need in this case because both conditions must be true to be a pass so if i understand the language correctly if say the project spec is between 93 and 97 it should say pass correct? im not sure whether KD2017 response would compare both but i am not that good at understanding. I know what i want it to do lol just not sure how to make it do it.
    Either approach will work. I'll try to illustrate my equation for understanding's sake:

    =IIf(abs([Project_Spec] - [TEST1_Compaction]) <= [Spec_Allowence_Compaction], "pass", "fail")
    abs() is the absolute value function

    given:
    spec = 95.0
    test_1 = 93.0
    test_2 = 97.0
    allowance = +-3.0

    Abs( spec - test_1 ) <= allowance
    Abs( 95.0 - 93.0 ) = Abs( 2.0 ) = 2.0 <= 3.0 Pass

    Abs( spec - test_2 ) <= allowance
    Abs( 95.0 - 97.0 ) = Abs( -2.0 ) = 2.0 <= 3.0 Pass

    For input validation you may want to ensure the user can't accidentally input negative numbers into the allowance fields.

    It's worth mentioning again, doing comparisons on floating point number (single, double datatypes) can give funny results due to rounding errors. My initial thought per post #4 was to use the round function like so but I'm not sure if it's a bullet proof solution... Hopefully others can chime in on this
    Code:
    =IIf(Round(Abs([Project_Spec]-[TEST1_Compaction]),6)<=Round([Spec_Allowence_Compaction],6),"pass","fail")

  8. #8
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30
    thank you for the great explanation and your time. all is working as it should now thank you everyone

  9. #9
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30
    okay this is simple i am sure KD2017 when i changed the box to your code i get a " #NAME?" error inside the box now any ideas>?

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    910
    That just means something is spelled wrong in the equation. Make sure all the names match up letter for letter with their corresponding field or form control. If it's still an issue you can upload a zipped copy of your db for review.

  11. #11
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30
    thanks man but that db is already about 500megs in size

  12. #12
    rshaw63 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    30
    okay figured that one out thanks again man have a great weekend.

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

Similar Threads

  1. Did I find a bug or do I just have my head up ...
    By MatthewGrace in forum Programming
    Replies: 4
    Last Post: 07-09-2017, 07:06 PM
  2. soccer table head-to-head query
    By sgramesh75 in forum Queries
    Replies: 2
    Last Post: 03-29-2017, 11:09 PM
  3. in Way over my head HELP
    By 91hrdbdy in forum Programming
    Replies: 8
    Last Post: 11-26-2013, 01:06 PM
  4. In over my head
    By TylerB in forum Access
    Replies: 2
    Last Post: 05-04-2011, 04:07 PM
  5. Help banging my head against a wall working on this
    By Brian Foshee in forum Programming
    Replies: 1
    Last Post: 06-04-2010, 08:30 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