Results 1 to 3 of 3
  1. #1
    wizzz_wizzz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    14

    Add Dashes In a String of Numbers

    Hi all,
    I have a table with a field(Part Number) of numbers 0123456789, 01234567....... I would like to add dashes in between such that the numbers becomes 012-34567-89 and 012-34567. I created a query with this expression

    PN_Mod: Left([Part Number],3)+'-'+Mid([Part Number],4,5)+'-'+Mid([Part Number],9,2)

    But I have a problem such that I get a dash even if my numbers are shorter => 012-34567- . How can I avoid getting a dash if there are no "digitits" to get 012-34567?



    Appreciate any advice.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You need an approach that will deal with all possibilities that you can see now, and the ones you don't. What if you have 012345678: 012-34567-8?

    If I wanted to do this, I'd create a function in a standard module and call it from the query, and parse the string in a way that will meet all the requirements. I say "if" because if your part number data is dash separated, then it probably should be stored that way as text. That would make this a one-time operation and further input might use an input mask (which I dislike).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Its much easier to put a string together than it is to parse it out especially if the string has different lengths and a varying number of sections.
    Assuming you have 2 to 3 sections in your part number it would be better to store them that way as Micron states. You could then use the Law of Propigating Nulls to get to format you want.
    For example if your fields are Prefix, Middle, and Suffix you could concatenate them as so . . .

    Code:
    (Prefix + "-") & (Middle) & ("-" + Suffix)
    The LOPN means that a null plus anything is a null (Note the use of + sign within the Parenthesis)
    So if you only had a prefix and a middle it would display as 123-45678. If there were also a suffix it would display 123-45678-910.
    This is especially helpful when you have a full name string like firstname, middlename, lastname, and suffix. Some people may have a middle name or initial or some people may have a suffix like Jr. or Sr.
    To keep the spacing even between names you would write it as
    Code:
    (firstname + " ") & (middlename + " ") & (lastname) & (" " + suffix)

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

Similar Threads

  1. vba to extract four numbers from a string
    By mainerain in forum Programming
    Replies: 3
    Last Post: 04-16-2020, 06:43 PM
  2. Dashes in phone numbers and zip codes
    By JMack in forum Access
    Replies: 8
    Last Post: 11-18-2013, 01:56 PM
  3. Sorting by Letters and then numbers with dashes
    By Analogkid in forum Queries
    Replies: 8
    Last Post: 04-29-2013, 05:05 PM
  4. how to decode a string of numbers
    By cjlieber in forum Programming
    Replies: 4
    Last Post: 04-16-2012, 06:50 PM
  5. Removing all letters or all numbers from string
    By Hayley_sql in forum Programming
    Replies: 2
    Last Post: 09-16-2009, 02:01 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
  •  
Other Forums: Microsoft Office Forums