Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    533

    Unanswered: Suggestions for Add Dashes Function

    I've written a function to convert a series of numbers without dashes to a part number with dashes. For searching the user may just enter the last 4 to 13 numbers. There can also be leading zeros in the result, which my function is not returning, even though dimensioned as a string? Would like any ideas for improving this and how to return the string with leading zeros.

    This is the format. From the right 4, 3, 2, 4 numbers
    5340-01-269-8943

    5340012698943
    Depending on the length entered could return:
    8943
    9-8943
    69-8943
    269-8943
    1-269-8943
    01-269-8943
    001-269-8943
    40-01-269-8943
    340-01-269-8943
    5340-01-269-8943



    Code:
     Function fAddNSNDashes(strNSN As String) As String
     Dim strPattern As String
     Dim intLen As Integer
     
      intLen = Len(strNSN)
      
      ReDim sFormat(5, 1)
      
      sFormat(0, 0) = 3
      sFormat(1, 0) = 4
      sFormat(2, 0) = 6
      sFormat(3, 0) = 9
      sFormat(4, 0) = 14
      sFormat(0, 1) = ""
      sFormat(1, 1) = "#-####"
      sFormat(2, 1) = "##-####"
      sFormat(3, 1) = "#-###-####"
      sFormat(4, 1) = "#-##-###-####"
      
      For i = 0 To 4
        If Not i = 0 Then x = 1 Else x = 0
        bLenght = (intLen <= sFormat(i, 0) And _
                  intLen > sFormat(i - x, 0) Or _
                  intLen <= 4)
        
        If bLenght Then
           strPattern = sFormat(i, 1)
           Exit For
        End If
      Next
      strPre = Format(strNSN, strPattern)
      If Left(strPre, 1) = "-" Then
        strPre = Right(strPre, Len(strPre) - 1)
      End If
      fAddNSNDashes = strPre
    End Function

    ~
    ~

    Bill

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where is this information going to be displayed? this would be done much easier via SQL.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Teddy
    Where is this information going to be displayed? this would be done much easier via SQL.
    I have a form where the PNs are displayed. The form already uses a list and options to set/change the data source to show parts received/open/all and by order number. I'm using the function with an unbound text field on the form for a Quick Find feature where the user can type the number without hyphens, hit the enter key to activate the search. The search is by the docmd.findrecord method in a sub procedure. Its just for speed to eliminate typing the hyphens. The search is working good. I may also incorporate the function for data entry on another form later, just to format data input, its not really for display purposes. With the search if the number is typed without hyphens the function inputs the hyphens to find the matching number record already in the parts table. Parts Form jpg

    The JPG shows the NSN field to be search and the NSN to search where the search value is entered 4 or more numbers.

    I'd like to find the answer for preventing the leading zeros from being dropped from the function return string value.

    ~
    ~

    Bill

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    replace the hash symbols "#" with a "0" should do the trick

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by healdem
    replace the hash symbols "#" with a "0" should do the trick
    Thanks a lot thats better. Except to make it work using the format() method I think I would need to have more patterns in the array. Place 3 works with an 8 digit number leading zero however will drop the zero on a 9 digit number. I think a re-write will be needed. I have another idea.

    sFormat(0, 1) = ""
    sFormat(1, 1) = "0-0000"
    sFormat(2, 1) = "00-0000"
    sFormat(3, 1) = "0-000-0000"
    sFormat(4, 1) = "0-00-000-0000"

    ------ New rewritten function ---------
    This is the more concise function. Uses 3 counters to keep track of string position, Array, and number of for loop iteration.
    Code:
     Function fNSNDashes(strNSN As String) As String
     Dim sLoc As Variant
     Dim intLen As Integer
     Dim strNew As String
     Dim n, i, y As Integer
     
      intLen = Len(strNSN)
      
      ReDim sLoc(3)
      
      sLoc(0) = 5
      sLoc(1) = 8
      sLoc(2) = 10
      
      n = 0
      For i = intLen To 1 Step -1
       y = y + 1
       If y = sLoc(n) Then
          strNew = "-" & strNew
          n = n + 1
       End If
       strNew = Mid(strNSN, i, 1) & strNew
      Next
      fNSNDashes = strNew
    End Function
    Last edited by savbill; 02-20-05 at 21:48.
    ~

    Bill

Posting Permissions

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