Parsing a Field

  • Thread starter Thread starter Sash
  • Start date Start date
S

Sash

Have a database from a client that contains a field with 8 characters that
identify a matching document. However, if there are two documents associated
with a client, the field become 16, 3 would be 24 characters, etc.

I need to break this down into separate fields. For example:

00000000100000000200000003 is currently stored as one field. I want to
store 00000001, 000000002, 000000003 in a separate table joining to client
data on a common key. I've parsed fields on comma or space, but am not sure
how to approach this on.

Any suggestions or a starting point would be greatly appreciated!
 
Have a database from a client that contains a field with 8 characters that
identify a matching document. However, if there are two documents associated
with a client, the field become 16, 3 would be 24 characters, etc.

I need to break this down into separate fields. For example:

00000000100000000200000003 is currently stored as one field. I want to
store 00000001, 000000002, 000000003 in a separate table joining to client
data on a common key. I've parsed fields on comma or space, but am not sure
how to approach this on.

Any suggestions or a starting point would be greatly appreciated!
Click to expand...

Use the Mid() function; see the VBA help for the topic.

Your client (or their database implementor) deserves forty lashes with a wet
noodle.
 
Assuming you have a Table named ‘Numbers’ and in there you have a field named
‘Numbers’, pop this SQL into a new Query:
SELECT Numbers.Numbers, Left([Numbers],9) AS LeftPart, Mid([Numbers],10,9)
AS MidPart, Right([Numbers],8) AS RightPart
FROM Numbers;

Good luck,
Ryan---
 
See if this gets you going:

Sub ParseIt()
Dim lngPosition As Long
Dim lngPrevStart As Long
Dim strInput As String

lngPrevStart = 1
strInput = "00000000100000000200000003"
For lngPosition = 1 To Len(strInput)
If Mid(strInput, lngPosition, 1) <> "0" Then
Debug.Print Mid(strInput, lngPrevStart, lngPosition -
lngPrevStart + 1)
lngPrevStart = lngPosition + 1
End If
Next lngPosition
Debug.Print Mid(strInput, lngPrevStart, Len(strInput) - lngPrevStart +
1)

End Sub

Here's what I get in the Immediate window:

ParseIt
000000001
000000002
00000003
 
Sash,

Here is a function that you can do some testing with and then use as much of
it is you want for your actual application. By uncommenting one of the
"strValue" assignments you can have the string to contain from 1 - 4 of your
8 character doc codes. When you run this code it will assign each 8
character string to one of the variables in the "strDocCodes" array. I will
then print that value to the Immediate window.

This code is just for testing purposes and it is up to you as to what you do
with each of the values that get assigned to the array.

Public Function ParseString()
Dim strValue As String

Dim strDocCodes() As String

Dim bytStrLen As Byte
Dim bytItemsCnt As Byte

Dim cntr, codeStart

'strValue = "00000001"
'strValue = "0000000100000002"
'strValue = "000000010000000200000003"
strValue = "00000001000000020000000300000004"

bytStrLen = Len(strValue)
bytItemsCnt = bytStrLen / 8
ReDim strDocCodes(bytItemsCnt)
codeStart = 1
For cntr = 1 To bytItemsCnt

strDocCodes(cntr) = Mid(strValue, codeStart, 8)
codeStart = codeStart + 8
Debug.Print strDocCodes(cntr)
Next

End Function

HTH

MR. B
askdoctoraccess dot com
 
Back
Top