Input masks for numerical data?

linsey
linsey used Ask the Experts™
on
Hi All
I would like to create an input mask for swimming times. These are expressed as minutes:seconds.tenths/hundredths of a second written in the format mm:ss.tenshundredths when presented as information for club members.  I've created an input mask but only as a text format as my help facility suggests that input masks are not available for numerical data.  At the moment I don't want to calculate as such using this data but eventually may want to create charts of improvements in performance so may need to subtract one from the other preserving the validity of the data.

I do need to be able to ask a query to select only records which have the fastest time from a database table of each swimmer, the stroke they have performed and their time.  The person who has given me the date has converted the swim times to seconds to do this and then used an expression to show the data in the format described above. Is their a way of inputting the data in my desired format but numerically so that the data can be processed properly?

Sorry this is long but nowhere have I read a solution that covers my problem.

Cheers

Linsey
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Your help facility must be missing this page then.

InputMask Property
               
You can use the InputMask property to make data entry easier and to control the values users can enter in a text box control. For example, you could create an input mask for a Phone Number field that shows you exactly how to enter a new number: (___) ___-____. It is often easier to use the Input Mask Wizard to set the property for you.

Setting

The InputMask property can contain up to three sections separated by semicolons (;).

Section Description
First Specifies the input mask itself; for example, !(999) 999-9999. For a list of characters you can use to define the input mask, see the following table.
Second Specifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for this section, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored.
Third Specifies the character that Microsoft Access displays for the space where you should type a character in the input mask. For this section, you can use any character; to display an empty string, use a space enclosed in quotation marks (" ").

In Visual Basic you use a string expression to set this property. For example, the following specifies an input mask for a text box control used for entering a phone number:

Forms!Customers!Telephone.InputMask = "(###) ###-####"When you create an input mask, you can use special characters to require that certain data be entered (for example, the area code for a phone number) and that other data be optional (such as a telephone extension). These characters specify the type of data, such as a number or character, that you must enter for each character in the input mask.

You can define an input mask by using the following characters.

Character Description
0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 Digit or space (entry not required, plus and minus signs not allowed).
# Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).
L Letter (A to Z, entry required).
? Letter (A to Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
. , : ; - / Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).
< Causes all characters to be converted to lowercase.
> Causes all characters to be converted to uppercase.
! Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.
\ Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

For a control, you can set this property in the control's property sheet. For a field in a table, you can set the property in table Design view (in the Field Properties section) or in Design view of the Query window (in the Field Properties property sheet).

Author

Commented:
Thanks for replying so quickly but that wasn't really my problem.  I can create an input mask for a text value and also for some custom number formats such as phone numbers which are not really numerical data as such but if I want an input mask for numerical forms of the swimming times so that I can compare them the wizard tells me that it is not possible to set an input mask for number fields.

 I created the mask that created the format 01:22.83 for example and it makes data entry easy- great. The reason I haven't used excel is that I am told that it isn't possible to create masks to make data entry simple - in my case typing in 6 numbers without having to type the seperators.  But want I need to do is get the times recognised as numbers so that I can take one time from another to find out how much improvement there is over time.  Without the input mask data entry will be slower and if I convert the times to seconds ie 82.83 the swimmers won't easily be able to relate to this as this is not the format used on gala sheets.  I have a database that someone gave me where they have listed all the swimmers times for each of their events as seperate records. I need to be able to set the database so that I can extract the fastest of each swimmers times and then put them into a ranked table with the fastest times of all the others.   Hope this isn't too long winded.

Commented:
To make a field numeric, hi-lite the table where the data resides, select design view, then next to the field change the data type to "number".
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
But will the data remain valid - ie in the minutes seconds tenths and hundreds form as this is not one of the standard formats in access. If it alters the meaning of the data then it won't recognise that that 01: represents sixty seconds and subtracting one record from another may lead to a false result.

Sorry - I realise that the best way is to try this but I am not at home with my data and very keen to get the problem sorted!  Thanks

Commented:
Right click on the table and select copy, then click off of it and right click and select paste.  It will ask you to name it.  Then if you mess up your data, you have a copy of your original table.

Author

Commented:
But will the data remain valid - ie in the minutes seconds tenths and hundreds form as this is not one of the standard formats in access. If it alters the meaning of the data then it won't recognise that that 01: represents sixty seconds and subtracting one record from another may lead to a false result.

Sorry - I realise that the best way is to try this but I am not at home with my data and very keen to get the problem sorted!  Thanks

Author

Commented:
I tried the last suggestion but the data was corrupted as I suspected. It didn't bear any resemblance to the original but I think the text input mask will work as it will sort in ascending order and gives me the lowest time first. What I think is really my problem is working out how to select with a query the fastest time in a particular stroke category and distance category to put in a rankings table. So if Jane Smith has 5 times for the 50m freestyle and Jane Doe has 10 times I want to only select the fastest times of each girl to put in th rankings table from all of the records.  I've been studying 'running microsoft access' for hours and have tried the totals function (min and max) but can't seem to get what I'm after.
Any suggestions gratefully received

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial