Results 1 to 11 of 11

VBA Format Append Leading Zeros

  1. #1
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69

    VBA Format Append Leading Zeros

    I have a subform with a text field that I am trying to append leading zeros.

    Table = [tblSampleLogIn]
    Table = [tblMycoData]
    Form = [frmSampleLogIn01]
    Form = [subMycoData] ; Field = [SampleID] Text

    Here is my code so far:

    Code:
    Private Sub SampleID_AfterUpdate()
    Dim srtSampleID As String
    
    Set srtSampleID = Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value
    
    Forms!frmSampleLogIn01!subMycoData!SampleID.Value = Format(Forms!frmSampleLogIn01!subMycoData!SampleID.Value, "000000")
    End Sub
    The text values will be less than 6 characters in length. I can get the immediate window to return the value in the field by entering the following into the immediate window:
    Code:
    Forms![frmSampleLogIn01]![subMycoData].Form![SampleID].Value
    But then, nothing else happens when the code runs.

    Ultimately, I need to have leading zeros in front of the values to sort the text values. The reason is because sometimes a numerical character is unknown, and will be substituted with a "?". Having leading zeros will help to properly sort the text values.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,819
    SampleID control event is on the main form and trying to set value of subform? Form/subform master/child linking should handle saving value to subform record. SampleID on main form is not Autonumber?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    ssanfu is offline VIP
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,394
    Format only affects the way data is displayed.

    If you want to change the text data to have 6 characters, you might think about concatenating 6 zeros to the value , then taking the right 6 using the Right() function.

    Code:
    Forms!frmSampleLogIn01!subMycoData!SampleID = Right( "000000" & Forms!frmSampleLogIn01!subMycoData!SampleID, 6)
    FYI, you don't need to add ".Value"..... it is the default property. (and saves on typing )
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,819
    ssanfu,

    It is correct that Format only affects display of value in a SELECT query or textbox ControlSource but when used in an equation to set a text value, the leading zeros will be retained. The expression can be in VBA code as shown or in an UPDATE action.
    Last edited by June7; 03-27-2012 at 05:31 PM.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    ssanfu is offline VIP
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,394
    Thanks June. I didn't know this. I've always used the concatenate and Right() function; I'll have to remember this and try it.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    I haven't tried the code yet, but was just wondering:
    Might it be better for me to store the data in the table AS IS, and then create a query to concatenate the leading zeros when it comes time to sort?

  7. #7
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    Just tried ssanfu's code, and it works! Thank you!

    Code:
    Forms!frmSampleLogIn01!subMycoData!SampleID = Right( "000000" & Forms!frmSampleLogIn01!subMycoData!SampleID, 6)
    I am still wondering though, could the same thing be achieved with a query?

  8. #8
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    Answered my own question: Yes, the same thing can be achieved using a query. And, I believe that I will use the query to concatenate the leading zeros, as opposed to having VBA add the leading zeros. Thank you so much!

  9. #9
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,819
    The expression can be in query or textbox ControlSource or in VBA. Calculate whenever needed or run UPDATE action to actually modify the value in table.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    raytackettsells is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    27
    Hey there, I searched all throughout the forums and this was the only one. I was wondering what would be the format in a query in the field which would calculate and place leading zeros in front making the field a specific number of bytes (i.e. whether the number is 4 or 6 digits, it would lead and keep it 7 bytes...

    So if it's 1234 and 56789 the number should be converted to 0001234 and 0056789. Just a question and was looking. That would be awesome and thanks ahead of time for any questions you help me with.

  11. #11
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,819
    Use as many zeros in the expression as you want. Try it.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing data with leading zeros
    By buckychudd in forum Import/Export Data
    Replies: 7
    Last Post: 11-04-2011, 11:26 AM
  2. Leading Zeros in Access 2010
    By aa_weber in forum Access
    Replies: 3
    Last Post: 10-16-2011, 10:17 PM
  3. Leading Zeros
    By dirtbiker1824 in forum Access
    Replies: 1
    Last Post: 03-14-2011, 01:16 PM
  4. Adding Leading Zeros
    By jo15765 in forum Access
    Replies: 13
    Last Post: 11-20-2010, 09:11 PM
  5. leading 'Zeros' in data
    By wasim_sono in forum Forms
    Replies: 3
    Last Post: 04-06-2009, 10:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums