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

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Data Validation Question....    
 
   
Sosul
post Oct 13 2006, 11:54 AM
Post #1

UtterAccess Addict
Posts: 118
From: Louisville, KY
I want to limit a user to only enter a number that is 3 number a "." and 2 more numbers, such as 811.20. How can I do this?

I tried Custom and the formual as 3 & "." & 2.
Go to the top of the page
 
+
fkegley
post Oct 13 2006, 12:02 PM
Post #2

UtterAccess VIP
Posts: 23,583
From: Mississippi
A Validation rule can do this. Select the relevant cells. Then click Data--->Validation... Set Allow to Decimal. Set Data to Between. Set the Minimum and Maximum values as you want. I used 100 for the Minimum and 999.99 for the Maximum. You'll also need to format the cell so it shows 2 decimals.
Go to the top of the page
 
+
Sosul
post Oct 13 2006, 12:26 PM
Post #3

UtterAccess Addict
Posts: 118
From: Louisville, KY
There are no minimum Maximun values. Actually they can even use v81.30, these are diagnosis codes.
Go to the top of the page
 
+
Troulcortex
post Oct 13 2006, 12:31 PM
Post #4

UtterAccess Guru
Posts: 946
From: Pennsylvania, USA
I would probably separate out the left and right side of this number, because it seems they represent different things. i.e.: 822.30

txtHead txtTail
822 30

You can then limit the field length to 3 for the txtHead field and 2 for the txtTail field.
This is in line with normalization rules and may help you do sorting/grouping later, as well.


hth,

Gabe
Go to the top of the page
 
+
niesz
post Oct 13 2006, 12:35 PM
Post #5

Utter A-fishin'-ado
Posts: 17,723
From: Cincinnati, Ohio, USA . . . ><((((°>
The way I do this is just keep a table of all the IDC9's. Then match the entry to the lookup table. You can even use a combo box and let them pick from a list that contains the description.
Go to the top of the page
 
+
Sosul
post Oct 13 2006, 12:36 PM
Post #6

UtterAccess Addict
Posts: 118
From: Louisville, KY
So I should write in forumlas that grab the numbers before the "." and the numbers after the "." and if either of those are 3 or 2 length then it pops up an error message?

Seems like a lot of work to do all of that.
Go to the top of the page
 
+
fkegley
post Oct 13 2006, 01:15 PM
Post #7

UtterAccess VIP
Posts: 23,583
From: Mississippi
Oh, I see. Then the only way to do this would be as Walter has posted. Put the possible codes in consecutive cells in a column. Assign a range name to those cells. Use the range name as the source of Validation Rule of List.
This will also be a lot of work, but there's no way to do this without a lot of work.
Go to the top of the page
 
+
Troulcortex
post Oct 13 2006, 01:46 PM
Post #8

UtterAccess Guru
Posts: 946
From: Pennsylvania, USA
Yeah, Walter's solution seems the most practical in your case.

Gabe
Go to the top of the page
 
+
Troulcortex
post Oct 13 2006, 01:48 PM
Post #9

UtterAccess Guru
Posts: 946
From: Pennsylvania, USA
Oops didn't realize I was in an excel forum.... My first comment is a little out of place, now..!!

Sorry!

Gabe
Go to the top of the page
 
+
Sosul
post Oct 16 2006, 07:25 AM
Post #10

UtterAccess Addict
Posts: 118
From: Louisville, KY
Ha ha ha.

Too true Troul!
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

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

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 22nd May 2013 - 02:42 AM