Re: How to Force Field Size
From: George Nicholson (JunkGeorgeN_at_msn.com)
Date: 05/28/04
- Next message: Michael Hopwood: "Re: Create a folder with Date"
- Previous message: Elwin: "Re: SQL Statement - Form/SubForm"
- In reply to: DOYLE60: "Re: How to Force Field Size"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 28 May 2004 14:34:35 -0500
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
-- George Nicholson Remove 'Junk' from return address. "DOYLE60" <doyle60@aol.com> wrote in message news:20040528144851.19841.00000083@mb-m02.aol.com... > 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 > > > > > > > > >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 > > > >>-----Original Message----- > >>try Format(123,"0000000000") > >> > >> > >>"DOYLE60" <doyle60@aol.com> wrote in message > >>news:20040528124318.15979.00000054@mb-m16.aol.com... > >>> 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 > >>> > >>> > >> > >> > >>. > >> > > > > > > > > > > > > > >
- Next message: Michael Hopwood: "Re: Create a folder with Date"
- Previous message: Elwin: "Re: SQL Statement - Form/SubForm"
- In reply to: DOYLE60: "Re: How to Force Field Size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
