UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Validation Rule, Office 2007    
 
   
accessdbguru
post Jun 7 2010, 09:51 AM
Post #1

UtterAccess Addict
Posts: 220
From: Houston, TX



I like to add a validation rule to only allow to enter values between 0 to 100.
I have this... "Between 0 to 100" but it still accepts decimal values. Suggestions?
Go to the top of the page
 
+
theDBguy
post Jun 7 2010, 09:53 AM
Post #2

Access Wiki and Forums Moderator
Posts: 57,600
From: SoCal, USA



Hi,
ry changing the field's data type to Integer.
Hope that helps...
Go to the top of the page
 
+
accessdbguru
post Jun 7 2010, 10:05 AM
Post #3

UtterAccess Addict
Posts: 220
From: Houston, TX



You mean go to FORMAT tab of the text box. I dont see integer there.
Go to the top of the page
 
+
theDBguy
post Jun 7 2010, 10:09 AM
Post #4

Access Wiki and Forums Moderator
Posts: 57,600
From: SoCal, USA



No. I meant go to the Design View of the table and change the DATA TYPE to Integer.
ope that makes sense...
Go to the top of the page
 
+
accessdbguru
post Jun 7 2010, 10:22 AM
Post #5

UtterAccess Addict
Posts: 220
From: Houston, TX



It calls a stored procedure into SQL Server. There is no Access table.
I wrote this but not working:
Dim Award As Integer
Award = Nz(Me.txtAward_Num.Value)
If Not (Award >= 0 And Award <= 100) Then
MsgBox "cannot update. enter values between 0 to 100 only"
End If
Go to the top of the page
 
+
Daryl S
post Jun 7 2010, 10:42 AM
Post #6

UtterAccess VIP
Posts: 2,270
From: Colorful Colorado



Try this:
ward = Int(Nz(Me.txtAward_Num.Value))
Actually, set your control to that, like this:
Me.txtAward_Num.Value = Int(Nz(Me.txtAward_Num.Value))
Go to the top of the page
 
+
theDBguy
post Jun 7 2010, 10:45 AM
Post #7

Access Wiki and Forums Moderator
Posts: 57,600
From: SoCal, USA



Sorry, didn't realize we were dealing with an SQL Server database.
ope Daryl's solution works for you.
Good luck!
Go to the top of the page
 
+
accessdbguru
post Jun 7 2010, 11:21 AM
Post #8

UtterAccess Addict
Posts: 220
From: Houston, TX



Thanks Daryl. Works good but I have to add more logic now. Let me explain you in more detail.
The text box should only accept values between 0 to 100 and SHOULD NOT accept percentages or decimals. example. 50%, or .45.
owever if someone enters
45% should automatically convert to 45
.60 should automatically convert to 60
.01 should automatically convert to 1
.001 should display msgbox "whole numbers only. no decimals"
less than 0 or negative values should give msgbox with values only between 0 to 100
101 or more should give msgbox with values between 0 to 100
thanks,
SMS
Go to the top of the page
 
+
Daryl S
post Jun 7 2010, 01:26 PM
Post #9

UtterAccess VIP
Posts: 2,270
From: Colorful Colorado



SMS -
ore complex issues need more complex code. Something like this:
If Me.txtAward_Num.Value > 0.005 and Me.txtAward_Num.Value < 1 Then
Me.txtAward_Num.Value = Me.txtAward_Num.Value * 100
End If
If Me.txtAward_Num.Value < 0 or Me.txtAward_Num.Value > 100 Then
MsgBox "Values must be between 0 and 100 only. Please re-enter."
End If
Go to the top of the page
 
+
accessdbguru
post Jun 7 2010, 02:06 PM
Post #10

UtterAccess Addict
Posts: 220
From: Houston, TX



Thanks Daryl. Works good.
Just hate to add 2 more logic:
-It is still allowing to enter 101% and more
- It is allowing to enter values in decimals example 55.5% should be rounded of to 56 instead

SMS
Go to the top of the page
 
+
lawmart
post Jun 7 2010, 03:29 PM
Post #11

UtterAccess VIP
Posts: 2,215
From: Vermont, USA



If the field links to a character field you could try an input mask such as ###;;
hope this helps.
Go to the top of the page
 
+
Daryl S
post Jun 8 2010, 08:31 AM
Post #12

UtterAccess VIP
Posts: 2,270
From: Colorful Colorado



SMS -
dd in this line between the two If/EndIf blocks (it will round off the number to zero decimal places):
Me.txtAward_Num.Value = Round(Me.txtAward_Num.Value,0)
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 20th August 2014 - 07:21 PM