My form displays the Employee Number correctly as 0049, yet when I refer to that field in my code it changes it to 49. I need the code results to show the full number with the proceeding zeros. I tried to format the declared value but this did not work. Any suggestions?
strEmpNumber = Format(Me.EmployeeNumber, "####")
The strange thing to me is that the number is formatted correctly in the underlying table so I don't understand why the zeros are being stripped
There is a difference between the value of a number, and how you DISPLAY that number in a form or report. In this case, for example, the two leading zero's have no meaning, no value. They are only there for DISPLAY purposes. So don't worry about them until you need to DISPLAY the values somewhere for users.
You tried to Format() the number, but Format(), again, is only for DISPLAY purposes and has nothing to do with the underlying value.
Where and why do you need to show this four-digit DISPLAY FORMAT?
===========================
Also, as Mike pointed out, you may be using the wrong data type anyway.
EmployeeNumber is probably not an actual number, in the sense implied in this discussion. You can easily prove it by adding two EmployeeNumbers together. Does that create a valid result? Perhaps easier to see and understand, add two telephone numbers together. Does that result in anything meaningful?
The point here is that text strings can consist of alpha characters, digits, or even a combination of alpha characters and digits. Your EmployeeNumber is really just a string of 4 digits and not an mathematically valid "number" at all.