Changing or editing the input mask but not mess up old data?

[ashwur]

I have a database in access where a field used to allow only 4 digit numbers separated by a slash. (ex: 4444/4444). However, we have now started to have numbers that can be 5 digits and 5 digits. (ex:55555/55555).

The input mask was previously 0000/0000. If I change it to 90000/90000, it changes my old data by moving the numbers forward in front of the slash (eg: 1234/5678 now looks like 12345/678). I have tried different combinations of where to place the 9 for the optional digit and how to enter data (from left to right using !) but it always changes my old data by moving the numbers in front of or behind the slash. It also tells me the data is then in the wrong format if I click in the old box (obviously) because it is missing required digits at the beginning or end then.

Is there a way to not have my old data change but allow for new 5 digit numbers to be entered?

[zmbd]

What is the typecast of the field in the table (long, text, etc...). I'm suspecting that you have a numeric field type from post.

[NeoPa]

I don't believe it's possible to have a single input mask that covers both styles of data. Essentially they are incompatible.

Is it practical to update all your existing data to 5 digits with leading zeroes then use that format going forwards? If not then you appear to be suffering from the problem (too frequently come across) of failing to allow for expansion in the original design.

If scrapping and starting again is not possible then you could consider losing the input mask completely and using VBA to check and format your data when input.

[zmbd]

neopa,

If the field type is numeric, then as you said. I suspect from what OP has posted, the field is Numeric(long). This is because the literal "/" in OP's data isn't stored in the numeric field and the mask will by default fill right to left with data display.

The other issue with the numeric, by definition, leading zeros are ignored in whole numbers. 0##### > #### not matter what OP does, the second 0 in the entry is of course a non-issue, and OP will still have data entry and display errors.

With a Text field, then the input mask can be worked around and in-fact should not effect the old data (at least I've not seen it happen in the databases I oversee); however, old data would have to be updated upon edit to match the new data format or changes abandoned. To clarify of in a TEXT we had 1234/5678 and OP needed to change this to 1239/5678 OP would have to update the field to some variation of #1239/#5678 or abandon the change.

My suggestion for OP is to take the single field and split it into two fields and join in the query with formatting as needed.
The data entry form's text box for data entry would hold the input mask and then using VBA take the input, string split(), and store into the correct fields.

Now why do I know this... We used to have fields that an old designer had the numeric count for three types of bacteria recorded in... some of these fields were text because we would enter TNTC and others were numeric and with various input masks... it's not normalized, and is absolutely a nightmare to maintain. I'm still trying to get them to reconsider moving to a normalized database. One more holdout and when retired I think I'll have a chance... five years and counting +)