1. Registered User
Join Date
Feb 2004
Posts
533

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
End Function```

~

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Where is this information going to be displayed? this would be done much easier via SQL.

3. Registered User
Join Date
Feb 2004
Posts
533
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.

~

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

5. Registered User
Join Date
Feb 2004
Posts
533
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.

#### Posting Permissions

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