Results 1 to 8 of 8
  1. #1
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68

    Remove Leading Zeros and Insert "-" before the last 3 digits

    So the field shows the account number as 00000000234342001.


    I need it to display in the query as 234342-001.

    In design view I am using Account Number: VAL([MST1_CAV_MBRDETL.ACCTNUM]) to remove the leading zeros.
    But I am unsure about the rest.


    In addition to this answer does anyone have a resource for learning how to manipulate strings and numbers to do this kind of thing? Like pull the first 3 characters and so on.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume the length of the number can vary? Basically you want to concatenate the left "x" digits, the dash, and the right 3 digits. Tools to do that are the Left(), Right() and Len() functions, and the & concatenation operator.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    yes the length of the number can vary so I would need to focus on the right 3 which stays constant (I always want the dash before the last 3 characters).
    Could you give me an example of the syntax?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the length less 3 would give you how many you want for the left side, would it not?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Yea I am using MBRNO: Left(Val([mst1_cav_emobldetl.mbrsep]),Len((Val([mst1_cav_emobldetl.mbrsep])))-3) in one column and
    MBRSEP2: Right([MST1_CAV_EMOBLDETL.MBRSEP],3) in another column and then placing the 2 together in a thrid column
    MBRSEP: [MBRNO]+" - "+[MBRSEP2]

    Problem is that if I uncheck the first 2 columns so they don't show the column showing the right combo (3rd column) won't work.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Why not just do it all in one formula?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    When I do it all in 1 formula the numbers with a lower than 7 or 8 digits cuts off a number. Don't ask me why. Works with an 8 digit number but if it is 00006050001 i will only get 605 - 001. if it is 00086050001 i get 86050 - 001 which is correct. I had to seperate them into the method above to get the correct result no matter how long the customer number is.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Show the formula you tried. It should produce the same result.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 07-12-2014, 02:02 PM
  2. Replies: 2
    Last Post: 04-19-2014, 02:42 AM
  3. Replies: 8
    Last Post: 11-25-2013, 03:35 PM
  4. Replies: 2
    Last Post: 12-04-2012, 01:03 AM
  5. How Do I Remove "Time" from a Datetime Field
    By James Parker in forum Queries
    Replies: 4
    Last Post: 01-06-2012, 03:05 PM

Posting Permissions

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