Exluding from control value in case statement, Possible??? (1 Viewer)

[Ray Spackman]

This is a little confusing but I will try to make it clear. I have the controls on a form whose values are filled by the user; AR, BS, and RR. My vba code compares two of these values at any given time, not all three, and returns a determined value to another control on the form if the comparison specifications are met for two of the three values.

The Problem:
If the comparison specification for AR and RR are met, the retruned value is correct but if the comparison specifications for BS and RR are met in a different case statement, the returned value does not change because the original comparison of AR and RR can still also be true, therefor returning that original value. The same happens whencomapring AR and BS.

So I am wondering if there is a vba operator that will allow me two exclude the value of a specific control. For instance in the second example where AR could be excluded so that only BS and RR can be compared regardless of the value of AR.

If this is not possible, can someone show me some code using Case statement and/or If statements that will allow me remedy this problem.

As always Thank You in advance.

 

[PNGBill]

May help if you can give some examples. Gets a bit with AR and BS....

If you have three values to compare and if any two can give a Positive way forward for the code then simple If Then, Else If should do the job.

If you want to compare all 3 combinations and then decide which one to take forward, then maybe you could create three Dim variables and store the result of each value.
If the result need to be weighted, then build your If Then so the preferred option is considered first.

You can combine and nest If Then and Case as much as you like but it seems like the issue here is your preference of which two to take forward.

 

[Ray Spackman]

I think your on to something with dim three variables, which I have done because the project is explicit, however, being kind of a novice with vba, I am not sure how to store and retrieve, or as you say, bring forward, the variables I am looking to compare. Here is a small example of what I am running into but keep in mind it gets more complicated as I add more comparsions.

Example: AT = 2.5 AR = 10 BS = 20 RR = 400

Code:
Select Case AT = 2.5
Case AR <= 15 and RR >= 376
IMBR1 = 2
IMBR2 = 1
(this returns okay becaue AR is <= 15 and RR is >= 376)

Case AR <= 15 or RR >= 376
IMBR1 = 1.75
IMBR2 = 1
(this returns okay because AR is <= 15 and if the RR value is changed to 350 it is no longer >= 376)

Case AR >= 76 and BS > 19
IMBR1 = 1
IMBR2 = 1
(this returns not okay because even if the AR value were changed to 80 and meet the requirement of being >= 76, the returned IMBR1 value is 1.75 and IMBR2 = 1 because RR is still >= 400 meeting the requirement of the second case statement)

Basically that is what I am running into and gets more confusing when adding more and/or comparisons that need to take place.

While waiting for your reply, I changed my code. I haven't tried it yet but maybe you can take a look at it ( I attached it), although I think your suggestion of storing and retrieving the values I need to campare is spot on; not need some examples on how to do that.

 

[Ray Spackman]

Just tried that code I attached and it does not work at all. Back to the drawing board.

 

[boblarson]

Your code is completely incorrect. Your usage of the Select Case is way, way off.

First off, with a correct Select Case statement you would be selecting the ONE item you are going to check.

So, this would be appropriate:

Select Case AT

Nothing more. No = 2.5, etc.

Select Case AT is telling Access you want to check the value in the AT variable.

The next part is each case statement. But each case statement needs to deal with ONLY that ONE variable.

Select Case AT
   Case 2.5
     ' do something here if the value of AT is equal to 2.5
   Case > 2.5
     ' do something here if the value of AT is greater than 2.5
   Case Else
     ' do something here if neither of those are applicable
End Select

So I think you need to rethink what you are trying to do. You probably are better off with a series of IF statements and then setting a boolean flag to skip any tests if any of them match but I'm not sure still by what you posted as to what the whole process would need.

 

[Ray Spackman]

Okay I understand somewhat. The reason for the Select Case AT = 2.5 is because that value can also be changed by the user. But when that value is changed the same AR BS and RR comparisons are still compared only returning different value for IMBR1 and IMBR2. I understand taht this may be incorrect which is why I am looking for guidance. Do you think If statements would be better or PNGBill' advice about storing and retrieving only the value I want to compare; in either case help by use of examples would be appreciated.

 

[PNGBill]

It is still difficult for me to understand what comparisons you are doing.
I would suggest, if possible, start with your first comparison and then store this result in a Variable.
Then do a 2nd comparison and store this result and so on.
Then you can compare the results and decide what action to take.

To do this you would need X number of variables to equal X number of comparisons.

One Variable may well be the Preferred Choice and you keep comparing this as you proceed and if the current choice is preferred, then you make the Preferred Choce to be the Current comparison and then go to the next comparison.
This would be a "running" comparison rather then line up all your comparisons and then start comparing them with each other.