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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 220
Joined: 11-June 08
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. Suggest
Go to the top of the page
 
theDBguy
post Jun 7 2010, 09:53 AM
Post#2


Access Wiki and Forums Moderator
Posts: 61,821
Joined: 19-June 07
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



Posts: 220
Joined: 11-June 08
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: 61,821
Joined: 19-June 07
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



Posts: 220
Joined: 11-June 08
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
Joined: 1-June 10
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: 61,821
Joined: 19-June 07
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



Posts: 220
Joined: 11-June 08
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
Joined: 1-June 10
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



Posts: 220
Joined: 11-June 08
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
Joined: 6-March 03
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
Joined: 1-June 10
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
 


RSSSearch   Top   Lo-Fi    24th April 2015 - 07:20 PM