Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I insert leading characters in a field?

Discussion in 'Information Technology' started by aceman62, Jul 28, 2006.

  1. aceman62

    aceman62
    Expand Collapse
    Guest

    I want to pad the field with leading zeros so it is 8 characters in length.
    If the user enters 123, I want it to default to 00000123.
     
  2. Loading...

    Similar Threads Forum Date
    General Leading Sikh activist discusses U.S. religious liberty Hard Talk Mar 28, 2013
    Professor Dhunda: Intellectual or Misleading? Hard Talk Feb 21, 2012
    India's Leading Export: CEO's Inspirational Stories Jul 23, 2011
    Pacific China Should Listen to Kissinger: You're on Top Now. Start leading Breaking News Jul 5, 2011
    SALDEF SALDEF Secures Apology From FBI Director For Misleading Remarks About Sikh Americans Sikh Organisations Dec 11, 2010

  3. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    Let the user enter it and let your form store it in your table as 123.
    Whereever, you then use this field, use the expression:

    Format([MyField],"00000000")


    --
    PC Datasheet
    Your Resource For Help With Access, Excel And Word Applications
    Over 1175 users have come to me from the newsgroups requesting help
    resource@pcdatasheet.com


    "aceman62" <aceman62@discussions.microsoft.com> wrote in message
    news:F8C782AB-72D6-4951-A631-FF2C37AC15E1@microsoft.com...
    >I want to pad the field with leading zeros so it is 8 characters in length.
    > If the user enters 123, I want it to default to 00000123.
     
  4. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Man:

    What PCD wrote may be a good solution. However, if you want the column to
    sort correctly, you would need to change the data. I would use
    Right("0000000" & CurrentValue, 8)

    Tom Ellison


    "aceman62" <aceman62@discussions.microsoft.com> wrote in message
    news:F8C782AB-72D6-4951-A631-FF2C37AC15E1@microsoft.com...
    >I want to pad the field with leading zeros so it is 8 characters in length.
    > If the user enters 123, I want it to default to 00000123.
     
  5. StopThisAdvertising

    StopThisAdvertising
    Expand Collapse
    Guest

    "PC Datasheet" <NoSpam@Spam.Com> schreef in bericht news:I7udg.7820$y4.762@newsread2.news.pas.earthlink.net...

    --
    To Steve:
    No-one wants your advertising/job hunting here!
    'Resource ???? 1175 users ???? Are you kidding ????
    ==>> You mean that 1175 users have been spammed by you ??

    To the original poster:
    Most people here have a common belief that the newsgroups are for *free exchange of information*.
    But Steve is a notorious job hunter in these groups, always trying to sell his services.

    He is known here as a *shameless liar*, with *no ethics at all*.
    If you provided a valid email address he might even spam you with an offer for payed help

    Before you intend to do business with him look at:
    http://home.tiscali.nl/arracom/whoissteve.html
    Over 925 !! users (rapidly increasing..) from the newsgroups have visited the website to read about this fake 'resource'

    Arno R
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    aceman62 wrote:
    > I want to pad the field with leading zeros so it is 8 characters in
    > length. If the user enters 123, I want it to default to 00000123.


    Is the filed a number type or a text type.

    If you want to sort or compute you need to use a number type. With a
    number type it is usually easy to just use formatting to display the number
    with the desired number of leading zeros.

    --
    Joseph Meehan

    Dia duit
     
  7. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Tom Ellison wrote:
    > if you want the column to
    > sort correctly, you would need to change the data. I would use
    > Right("0000000" & CurrentValue, 8)


    To finish the job properly:

    Make the column fixed width 8 characters i.e. CHAR(8) rather than
    VARCHAR(8).

    Use a validation rule to disallow non-numeric characters (make it
    implementation-independent, i.e. handle both 'ANSI mode' and other
    mode, by avoiding wildcard characters):

    my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    Jamie.

    --
     
  8. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    If you need to sort, include both the 3-digit field value and the formatted
    8-character value. Sort on the 3-digit field value. Or, you can use this
    expression in your query and sort on the same field:
    ValueIWant:Val(Format([MyField],"00000000"))

    --
    PC Datasheet
    Your Resource For Help With Access, Excel And Word Applications
    Over 1175 users have come to me from the newsgroups requesting help
    resource@pcdatasheet.com


    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:ODJK%23RHgGHA.1264@TK2MSFTNGP05.phx.gbl...
    > Dear Man:
    >
    > What PCD wrote may be a good solution. However, if you want the column to
    > sort correctly, you would need to change the data. I would use
    > Right("0000000" & CurrentValue, 8)
    >
    > Tom Ellison
    >
    >
    > "aceman62" <aceman62@discussions.microsoft.com> wrote in message
    > news:F8C782AB-72D6-4951-A631-FF2C37AC15E1@microsoft.com...
    >>I want to pad the field with leading zeros so it is 8 characters in
    >>length.
    >> If the user enters 123, I want it to default to 00000123.

    >
    >
     
  9. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Jamie.

    > To finish the job properly:
    >
    > Make the column fixed width 8 characters i.e. CHAR(8) rather than
    > VARCHAR(8).
    >
    > Use a validation rule to disallow non-numeric characters


    Dang! You don't much like Access users and developers, do you? ;-)

    You would rather the users type in all eight digits, including the leading
    zeros? You would rather the developer store eight characters (or 16 if
    Unicode compression is avoided), and create a validation rule to ensure that
    only digits are stored in the column, instead of storing a four byte Long
    that needs no such Validation Rule or leading zeros? All this so that the
    developer doesn't have to write a simple formatting function that displays
    the digits with leading zeros? (There's no sorting problem with the digits
    not lining up properly, so one doesn't have to worry about this column's
    sort order not being correct.)

    > my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'


    I must have the buggy version of Access because when it's in ANSI mode this
    syntax automatically gets rewritten as:

    "my_col" ALike '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    .. . . which obviously doesn't work. I have Access 2003 SP-1 installed on
    this computer.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148643768.544464.31480@i40g2000cwc.googlegroups.com...
    >
    > Tom Ellison wrote:
    >> if you want the column to
    >> sort correctly, you would need to change the data. I would use
    >> Right("0000000" & CurrentValue, 8)

    >
    > To finish the job properly:
    >
    > Make the column fixed width 8 characters i.e. CHAR(8) rather than
    > VARCHAR(8).
    >
    > Use a validation rule to disallow non-numeric characters (make it
    > implementation-independent, i.e. handle both 'ANSI mode' and other
    > mode, by avoiding wildcard characters):
    >
    > my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    >
    > Jamie.
    >
    > --
    >
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    '69 Camaro wrote:
    Hi Gunny,

    > You would rather the developer store eight characters...
    > instead of storing a four byte Long


    Yes. It's a basic data modelling approach: if the data in the reality
    is fixed width text then model it as fixed width text. I'm not against
    exploiting the software to perform integer operations on text data
    which contain only numeric characters, in which case store it as fixed
    width text as cast it as integer at the developer's convenience.

    > Dang! You don't much like Access users and developers, do you? ;-)
    > You would rather the developer store eight characters (or 16 if
    > Unicode compression is avoided), and create a validation rule to ensure that
    > only digits are stored in the column, instead of storing a four byte Long
    > that needs no such Validation Rule or leading zeros?


    If storage has become such an issue that you will consider modelling
    fixed width text as integer to save disk space, it is probably time to
    port to a platform more capable than Access/Jet.

    > All this so that the
    > developer doesn't have to write a simple formatting function that displays
    > the digits with leading zeros?


    That's not my point. I always say that the developer(s) should write
    both 'server side' procedures and 'client side' as appropriate e.g.
    more appropriate to do text formatting on the 'client side'. As a
    part-time developer myself, I'm not afraid of putting in the extra
    effort :)

    > You would rather the users type in all eight digits, including the leading
    > zeros?


    No. I would think it would be better to handle such formatting (e.g.
    padding with zeros) on the 'client side', especially in Access/Jet
    where you only get to perform one SQL command per procedure. However, I
    do agree that the 'server side' procs should handle data without the
    leading zeros. For example:

    CREATE TABLE Accounts (
    account_nbr CHAR(8) NOT NULL PRIMARY KEY,
    CONSTRAINT account_nbr__pattern
    CHECK (account_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
    )
    ;
    CREATE PROCEDURE AccountAdd (
    arg_account_nbr CHAR(8)
    )
    AS
    INSERT INTO Accounts (account_nbr)
    VALUES (RIGHT$('00000000' & arg_account_nbr, 8))
    ;
    EXECUTE AccountAdd '5'
    ;
    EXECUTE ACCOUNTADD 55
    ;
    EXECUTE AccountAdd 'Will fail'
    ;

    That last one generates an error which can be trapped on the 'client
    side'. Here's an alternative approach where the error is avoided and
    only the number of rows affected need be tested:

    DROP PROCEDURE AccountAdd
    ;
    CREATE PROCEDURE AccountAdd (
    arg_account_nbr CHAR(8)
    )
    AS
    INSERT INTO Accounts (account_nbr)
    SELECT DISTINCT RIGHT$('00000000' & arg_account_nbr, 8) AS account_nbr
    FROM Accounts
    WHERE RIGHT$('00000000' & arg_account_nbr, 8)
    LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    ;

    > I must have the buggy version of Access


    OK, here's a demo of my SQL code which doesn't use Access:

    Sub testGunny()
    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    With cat
    .Create _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\DropMe.mdb;"

    With .ActiveConnection

    .Execute _
    "CREATE TABLE Accounts ( account_nbr CHAR(8)" & _
    " NOT NULL PRIMARY KEY, CONSTRAINT" & _
    " account_nbr__pattern CHECK (account_nbr LIKE" & _
    " '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')" & _
    " );"

    .Execute _
    "CREATE PROCEDURE AccountAdd ( arg_account_nbr" & _
    " CHAR(8) ) AS INSERT INTO Accounts (account_nbr)" & _
    " VALUES (RIGHT$('00000000' & arg_account_nbr," & _
    " 8)); "

    .Execute _
    "EXECUTE AccountAdd '5';"

    .Execute _
    "EXECUTE AccountAdd 55;"

    Dim lRows As Long
    On Error Resume Next
    .Execute _
    "EXECUTE AccountAdd 'Will fail';", lRows
    MsgBox _
    "Error: " & _
    IIf(Len(Err.Description) = 0, "(none)", _
    Err.Description) & vbCr & vbCr & _
    "Rows affected: " & CStr(lRows)
    On Error GoTo 0

    .Execute _
    "DROP PROCEDURE AccountAdd;"

    .Execute _
    "CREATE PROCEDURE AccountAdd ( arg_account_nbr" & _
    " CHAR(8) ) AS INSERT INTO Accounts (account_nbr)" & _
    " SELECT DISTINCT RIGHT$('00000000' & arg_account_nbr," & _
    " 8) AS account_nbr FROM Accounts WHERE RIGHT$('00000000'" & _
    " & arg_account_nbr, 8) LIKE
    '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';"

    lRows = 0
    On Error Resume Next
    .Execute _
    "EXECUTE AccountAdd 'Will fail';", lRows
    MsgBox _
    "Error: " & _
    IIf(Len(Err.Description) = 0, "(none)", _
    Err.Description) & vbCr & vbCr & _
    "Rows affected: " & CStr(lRows)
    On Error GoTo 0

    End With
    End With
    End Sub

    Jamie.

    --
     

Share This Page