input mask

[maxmangion]

let's say i have a field, in which i store and identity card number. This number may consist up to 7 digits (of which 3 are mandatory) plus 1 letter (mandatory) at the end. Thus a valid identity card number may be the following: 1234567M, 123M

Eventually, since the field must always contain a letter, i set the data type to Text with field size of 8 ... and i set the inout mask as follows:

9999000L (since the first 4 digits are mandatory). With this input mask, if i have an ID Number of 123M, i have to input it as 0000123M.

Although, I would like to have the leading zeros, is it possible that during data entry time, i would simply type 123M, and i will get the zeros automatically, after the field loses the focus, rather than having to type them myself ?

Thank You

 

[zestygirl]

Yes, for leading zeros, put the placeholders in the Format section of the field properties in table design view...I haven't gone in and tried it, but, if you wanted a 1 to come up as 0001 you would simply enter 0000 in the format field.

 

[maxmangion]

unfortunately, your suggestion does not solve my current problem, with my input mask, because when i am inputting data, i would still need to type the 0s myself, in order to get the desired id number.

 

[zestygirl]

Seeing as you want to have 2 different types of formats, I don't think there will be 1 input mask that you can use that will be "smart" enough to identify one or the other.

Rather, you can periodically run a query that filters out any records containing fewer than 5 digits, and tacks on the leading zeros, then, make a new table which overwrites your original - complete with all your zeros.

For example, the identity code field in the query would filter as >99999 (or whatever accommodates the number format you have....you'll need to play with it - perhaps >=0000a and <=9999z. You get the idea.)

Create a new field - calling it identity code2 or whatever. it would be an expression: identitycode2="0000"&[identity code]

Run as a make table. Include the calculated field identity code2 (checked), not the original identity code field (unchecked).

After you've made new records, filter out the original 8 digit records - of course, can be done many ways. Append to your new table... Rename the new field to match the original field name, and the new table to overwrite the original table.

Sounds like a lot of work, but, if you have a macro and attach is to a button on your form, can process lickety-split.

Anyone got any better ideas?

 

[maxmangion]

does anyone have any other suggestion regarding my original question ?

zestygirl:

to be honest, i did not try using your suggestion, however, i can see that you have hard coded 4 zeros ("0000") ... eventually, the need for the zeros would vary because for example: 1234M and 123456M, the first would need 3 leading zeros, while the latter would only need 1 leading zero. Therefore the idea of hard code the amount of leading zeros is not a solution for this problem. ... however, i still appreciate your suggestion.

Any further advice/suggestion would be appreciated.

Thank You

 

[KenHigg]

Can you do this in the onChange or beforeUpdate event?

kh

 

[maxmangion]

yes i have no problem in using those events to solve my problem. However, how shall i go about it ? i.e. what should be done on those events.

 

[KenHigg]

Hum... Let me back up a bit...

Would it work to split these two out into seperate flds?

kh

 

[KenHigg]

Or something like:

select case len(Myfld)
  case 4
     myfld = "0000" & myfld
  case 5
     myfld = "000" & myfld
  case 6
     myfld = "00" & myfld
  case 7
     myfld = "0" & myfld
end select

???
ken

 

[maxmangion]

to be honest, it is not something, which i need to do in a particular database right now. However, I was thinking about it for future reference. Actually, in a situation that comes to mind, it would not be advisable to split the data in two fields.

Actually, i've seen this feature implemented in an Oracle database in the past, where a field could hold up to 8 digits ... and when you have type for example 1234 and click the retrieve button, it would have automatically be converted to 00001234. Similarly, if the number was 123, it would have been converted 00000123.

The reason which i was thinking of was this, let's say you are inputting personal details about a person. Eventually, i do not wish that a user would need to count manually how many leading zeros he should enter in a field, so that all value in that field would contain 8 digits.

 

[KenHigg]

So would something like the select case example work?

kh

 

[maxmangion]

it might be. to be honest, i did not try using code to achieve it. Actually, as i said i was using the input mask, and i was curious if it could have been done with the input mask ... hence i created this thread.

Actually, i am sure that with some code, it can easily be achieved, but i wanted to see if it also could be done by using the input mask.

 

[maxmangion]

oops, i didn't notice you posted a Case example ... at first glance, i would say that yes it works (although, i am not sure if it is the proper way of doing it) ... thank you for the suggestion, at least there is one way for sure

 

[KenHigg]

I would think this is going to be a very critical pc of data. In which case I would do a function to put it under the microscope, checking each character, etc.

kh