UtterAccess.com
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,270
From: SoCal, USA



Hi,

Try 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,270
From: SoCal, USA



No. I meant go to the Design View of the table and change the DATA TYPE to Integer.

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

Award = 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,270
From: SoCal, USA



Sorry, didn't realize we were dealing with an SQL Server database.

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

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

More 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 ###;;

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

Add 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: 30th July 2014 - 08:17 PM