Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Need To Check If A Value Entered In A Field Is Within Range

Posted by: Dave14867 Jul 17 2017, 08:01 AM

I need help on checking to see if a value entered into a field is within a given range of 67-71. I have looked for the VBA code and I can't seem to find it. Essentially, if the value is within the specified range, i want to change the backcolor, if not use a different backcolor.



Posted by: doctor9 Jul 17 2017, 08:22 AM


Have you tried Conditional Formatting on the control? This sounds like a perfect situation for it. Open the form in design view, and select the control (fields are in tables, controls are on forms). Select the Format tab and click on Conditional Formatting. Click on New Rule in the dialog box, and the default rule should be changing the format if the value is between two numbers.

Hope this helps,


Posted by: GroverParkGeorge Jul 17 2017, 08:23 AM

I suggest you use Conditional Formatting, not VBA to do this.

(As Dennis has already suggested.)

Posted by: Dave14867 Jul 17 2017, 08:59 AM

Can I then modify additional fields based on the backcolor of that filed after conditional format? What I mean is, can I make another field "Yes" and a different field "No" and vice versa?

Posted by: doctor9 Jul 17 2017, 09:05 AM


If you want another control to say "Yes" when the value in the first control is between 67 and 71, you would base the "Yes"/"No" expression on the first value being between 67 and 71, not on the conditional formatting of another control.

Hope this helps,


Posted by: GroverParkGeorge Jul 17 2017, 09:31 AM

Right, go to the base criteria (values between 67 and 71) for all conditionals, don't try to cascade them.

However, I'm going to add a caution about putting actual values into controls on a form, as opposed to FIELDS in a table.

I hope what you plan to do is DISPLAY the "Yes/No" values in unbound controls, not into fields in the underlying table. Is that the idea?

Posted by: Dave14867 Jul 17 2017, 10:11 AM

Currently they are in fields in the table, why is that an issue? (Is that considered redundant data? ) I guess it would now that I am thinking about it. I would like to be able to do everything with VBA if that is possible rather than the conditional formatting.

Posted by: Dave14867 Jul 17 2017, 10:30 AM

Attached is a file with the form and table in it. I want to color code the background of the "StartPres1" field (control) based on the value entered (vbgreen if within, vbyellow if out) and if the result is within, then "StartPres1PassFail" should be "Yes" and "StartPres1SubReq" should be "No". ( 51.15K ): 4

Posted by: GroverParkGeorge Jul 17 2017, 10:40 AM

Yes, they are redundant, and therefore not desirable. You can do it, but it's a waste of resources.

Why do you want to do it in VBA, which I would consider to be "the hard way"?

Certainly, you can do it in VBA, somewhat like this.

Private Sub txtYourFieldNameGoesHere_AfterUpdate()

If Me.txtYourFieldNameGoesHere Between 67 and 71 Then
    Me.txtYourOtherFieldNameGoesHere.BackColor = vbGreen
    Me.txtYourOtherFieldNameGoesHere.BackColor = vbYellow
End If

If this is, however, on a form in continuous view, it would ONLY work with conditional formatting anyway.

Posted by: Dave14867 Jul 17 2017, 11:41 AM

I believe I have it figure out.

Private Sub StartPres1_AfterUpdate()
If Me.StartPres1 >= "67" And Me.StartPres1 <= "71" Then
Me.StartPres1.BackColor = vbGreen
Me.StartPres1PassFail = "Pass"
Me.StartPres1SubReq = "No"
Me.StartPres1.BackColor = vbYellow
Me.StartPres1PassFail = "Fail"
Me.StartPres1SubReq = "Yes"

End If
End Sub

I need to add a check to make sure that "StartPres1" is populated also.

Does this look correct? It seems to work OK but does anyone see anything wrong with the code?



Posted by: GroverParkGeorge Jul 17 2017, 11:49 AM

If it works, you're good to go.

Continued success with your project.

Posted by: Dave14867 Jul 17 2017, 12:23 PM

Thanks for the assistance, if nothing else, you folks got me thinking in ways I hadn't been previously.


Posted by: tina t Jul 17 2017, 02:53 PM

If Me.StartPres1 >= "67" And Me.StartPres1 <= "71" Then

coding the numeric characters as strings (Text data type) instead of numbers (Number data type) may come back to bite you, if you find yourself needing to use different sets of low/high values. for instance, if you sort Text values "1" through "20", from low to high, you'll get the following:


if it's not an issue in the data you're working with, or if that's what you actually want, then you're good to go. thought i'd point it out, just in case...