Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Thanked 0 Times in 0 Posts

    Run code to add leading zeros (Access2003)

    I have a table that I am making (thanks to your help) by appending data from text file into the shell.
    EMPLIDs in this table are text and having lots of leading zeros.
    However I need this table for several queries with EMPLID as numeric field.

    So I have decided to insert another column into the table and have all the EMPLIDs altered.
    I have code for this but I can't think of how to use it.

    I can have form and push the button and run the code but first I need to insert column with same data - different name and have it text datatype.

    I am lucking logic here. What would be agood way to proceed? Thanks

    Function emplid5(dblEmplid As Double)
    Dim strEMPLID As String

    strEMPLID = dblEmplid

    Select Case Len(strEMPLID)
    Case Is = 1
    emplid5 = "0000" & strEMPLID
    Case Is = 2
    emplid5 = "000" & strEMPLID
    Case Is = 3
    emplid5 = "00" & strEMPLID
    Case Is = 4
    emplid5 = "0" & strEMPLID
    Case Is = 5
    emplid5 = strEMPLID
    End Select
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Run code to add leading zeros (Access2003)

    I'm confused. You state that you have a text field and want to change it to numeric.
    But the function that you posted converts numeric to text.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Connecticut, USA
    Thanked 0 Times in 0 Posts

    Re: Run code to add leading zeros (Access2003)

    You could use the following formula to populate your field.:

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Thanked 0 Times in 0 Posts

    Re: Run code to add leading zeros (Access2003)

    Sorry for the confusion.
    I had resolved this (all by myself! Imagine that:-))

    If anyone ever need this - I am sharing:

    Dim myYear As Long
    Dim myMonth As Long
    Dim myDay As Long
    Dim LastDay2MonthsAgo As Date

    myYear = Year(Date)
    myMonth = Month(Date)
    myDay = Day(Date)
    LastDay2MonthsAgo = DateSerial(Year(Date), Month(Date) - 1, 0)

    DoCmd.RunSQL "Delete * FROM TBL_5" (TBL_5 is a copy of the TBL with columd id_field_text added)
    DoCmd.Rename "TBL" & " " & Format(LastDay2MonthsAgo, "mm_dd_yyyy"), acTable, "TBLPrior"
    DoCmd.Rename "TBL Prior", acTable, "TBL"
    DoCmd.TransferText acImportDelim, "TBL_SYSTEMFILE", "TBL_5", "C:text16.txt"
    DoCmd.OpenQuery "Update_TBL_5_id_field", acViewNormal, acReadOnly
    DoCmd.CopyObject , "TBL", acTable, "TBL_5"


    My table TBL_5 has id_field that is text (after uplod from text file).
    Sometimes I need this field to be numeric.
    I have created new field in TBL_5 - id_field_no

    Query Update_TBL_5_id_field is updating field id_field_no - simply dumping data from id_field into numeric field.

    Now mission accomplished - I have table with 2 id_fields -
    1) id_field as text
    2) id_field_no as number.

    I have to check all of my queries now and edit joins. Lots of work!

    Thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts