UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Check If A Value Entered In A Field Is Within Range, Access 2010    
 
   
Dave14867
post Jul 17 2017, 08:01 AM
Post#1



Posts: 31
Joined: 28-November 16



Hello,
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.

Thanks

Dave
Go to the top of the page
 
doctor9
post Jul 17 2017, 08:22 AM
Post#2


UtterAccess Editor
Posts: 17,756
Joined: 29-March 05
From: Wisconsin


Dave,

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,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 08:23 AM
Post#3


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


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

Attached File  Conditionalformatting.png ( 30.07K )Number of downloads: 3


(As Dennis has already suggested.)

--------------------
Go to the top of the page
 
Dave14867
post Jul 17 2017, 08:59 AM
Post#4



Posts: 31
Joined: 28-November 16



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?
Go to the top of the page
 
doctor9
post Jul 17 2017, 09:05 AM
Post#5


UtterAccess Editor
Posts: 17,756
Joined: 29-March 05
From: Wisconsin


Dave14867,

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,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 09:31 AM
Post#6


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


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?

--------------------
Go to the top of the page
 
Dave14867
post Jul 17 2017, 10:11 AM
Post#7



Posts: 31
Joined: 28-November 16



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.
Go to the top of the page
 
Dave14867
post Jul 17 2017, 10:30 AM
Post#8



Posts: 31
Joined: 28-November 16



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".Attached File  Example_Data.zip ( 51.15K )Number of downloads: 0
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 10:40 AM
Post#9


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


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.

CODE
Private Sub txtYourFieldNameGoesHere_AfterUpdate()

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


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

--------------------
Go to the top of the page
 
Dave14867
post Jul 17 2017, 11:41 AM
Post#10



Posts: 31
Joined: 28-November 16



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"
Else
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?

Thanks

Dave
This post has been edited by Dave14867: Jul 17 2017, 11:43 AM
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 11:49 AM
Post#11


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


If it works, you're good to go.

Continued success with your project.

--------------------
Go to the top of the page
 
Dave14867
post Jul 17 2017, 12:23 PM
Post#12



Posts: 31
Joined: 28-November 16



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

Dave
Go to the top of the page
 
tina t
post Jul 17 2017, 02:53 PM
Post#13



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


QUOTE
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:

1
10
11
12
13
14
15
16
17
18
19
2
20
3
4
5
6
7
8
9

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...

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2017 - 08:34 AM