How to Force Field Size

  • Thread starter Thread starter DOYLE60
  • Start date Start date
D

DOYLE60

I need to create a field that will have 10 numeric digits with leading zeros.

For example:

0000989830
0000908871

That sort of thing. I can force it to be numeric using 10 zeros lined up in
the input value, but I don't see how to force it to have to have all 10 spots
taken up. In other words, a user could enter only a 6 digit number.

This is being done to transfer data to a mainframe. And for various reasons I
do with to have the leading zeros appear in the table fields.

Thanks,

Matt
 
to expand on Phil's answer... use the AfterUpdate() event
of the control to reformat the user's entry.

Sub txtMyControl_AfterUpdate()
Me!txtMyControl = Format(Me!txtMyControl,"0000000000")
End Sub
 
Thanks. But perhaps I should explain something a bit. In my database, my PO
Number was five numbers plus an optional letter, such as:

11987
11988
11989
11990
11990A
11990B
11990H
11991
91324A
etc.

My new system will have a PO number that has 10 numbers, including leading
zeros that are stored. The table will be:

0000123002
0000123003

In order to load my old data into the new system, I want to create a second PO
number in Access that just sits there and does not really do anything until we
transfer the data. I have decided to change my numbers into the 10 digits by
asssinging ones without a letter a 0 at the end and the ones with a letter to a
numeric value for that letter. A = 1, B = 2. All repeats were found and taken
care of by hand. So few, no problem.

So I created a query and created the new PO numbers already. But going
forward, on all new POs in my system, I want the data entry people to create
the second PO number as well as the real PO number. So, I simply want them to
have to enter 10 digits and 10 digits only without repeating a previous number.


So, no I do not want the users to enter, 19782 and have the system
automatically put in 0000018782. Things are more complicated than that.

So its a text field that the table must define as being 10 digits.

I'm not sure what to do with Format (123,"0000000000").

Thanks,

Matt
 
Make sure your field is defined as a text field, not numeric. Numeric fields
will not *store* leading zeros, text fields will. Unless you are planning
on adding, subtracting, dividing or multiplying PO numbers, there is no need
to treat them as numbers.

in the appropriate BeforeUpdate event:
ValueStoredInTable = LeftPadToLength(cstr(ValueInputByUser), 10, "0")

Public Function LeftPadToLength(strVal As String, intLength As Integer,
strChar As String) As String
'Add strChar to strVal until it is the specified Length
On Error GoTo ErrHandler

Dim i As Integer

i = intLength - Len(strVal)
If i > 0 Then
LeftPadToLength = String$(i, strChar) & strVal
Else
LeftPadToLength = strVal
End If
ExitHere:
Exit Function
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Custom Error message", vbCritical + vbOkOnly,
"Unexpected Error"
Resume ExitHere
End Select
End Function
 
Thanks George. But I get an error on the phrase "ValueInputByUser." I'm using
Access 1997, by the way. Was I supposed to substitute something there?

in the appropriate BeforeUpdate event:
ValueStoredInTable = LeftPadToLength(cstr(ValueInputByUser), 10, "0")
Click to expand...


Matt
 
Yes, substitute the value you are using for your PO number, whether it is
actually input by user or generated by the system.
(If you are doing this in a control BeforeUpdate event, you can probably use
the value of the control.)

BTW, ValueStoredInTable should also be replaced by a reference to the
underlying field (if the control field is unbound) or the control (if bound)
 
Back
Top