SetValue with Formatting

[F1stman]

Hello All,
I have a field in my database called Account#. Despite the name, there are
times when Account# is actually descriptive text. For this reason, normal
formatting or an input mask will not work to properly format the numerical
Account #'s. Formatting must be like so: @@-@@-@@ (or for "1234567890",
"12-34-56-78-90"). Please note that the real account# is 29 numbers so there
is not chance of accidental formatting of text.

Is there a way that I can set script on the lost focus action for the text
box where the data is entered such that dashes are inserted and the value is
saved in the table with dashes?

I have tried fancy IF functions with Formatting and InputMask adjustments
but this only solves the problem on the form and leaves report Account#'s
unformatted.
Below is what I have tried so far.

***
Private Sub Account_Code3_LostFocus()
If Me.Account_Code3 Like "#############################" Then
Me.Account_Code3.InputMask = "##-##-#-######-########-##-######-##"
End If
End Sub
***

Many thanks in advance.
Adam Kemp

 

[SteveM]

The problem you are having is really a product of bad design. You should not
use a field to store such inconsistent data, especially a field such as
Account#.
You would be better to create another field. In fact, you should probably
have another table for non-account accounts...

Steve

 

[F1stman]

I am working within the constraints of an already existing system for the
department I am designing this for. For a number of reasons they need this
data to be stored in the same table. Thanks for the help.