I NEED A CODE TO SEPARATE NUMERIC STRINGS, FROM STRINGS IN PARENTH

[Guest]

I need a code that will help me to separate numeric strings from other
numeric strings parentheses that are mixed up in one field within the table.

For instance:
(a) 68B (1,2) 69B(1,2) 70B(1,5-8)
(b) 1173.02(1.01, 1.04,71.08 etc)
(C) 110 (1)

These three examples shows a vivide pix of how the records are wrongly
stored in the table. I need to separate those in parentheses from those that
are not in parentheses. I have thousands of records that I cannot just create
a new field in the table and separate them manually.

Trying to use instring function, some have spaces while others don't, some
have letters some don't, some have decimal while others don't. Worst of all
their string lengths are not thesame. There is absolutely no uniformity for
those records in that field.

And I'm required to resolve this problem by creating a text box for those in
parentheses and those not in parentheses separately and name them
differently, ie they will function alike but separately. The user will enter
and retrieve their information from different textbox. The numerics that are
not in parentheses should be displayed in a different textbox from those in
parentheses. And I intend to put a strong check on the user so that he cannot
enter data any how he wants.

I'm not yet a Pro in Access and VB this is my first program. Please, can
someone out there help me out from this mess!

 

[John Spencer]

No solution, but a suggestion.

You show examples of what you have. You don't show what you expect to get.
For (a), do you expect two fields that look like the following?
Field1: 68B 69B 70B
Field2: (1,2) (1,2) (1,5-8)

Or do you expect multiple records from the value where the first record has
68B and (1,2)?

 

[Guest]

John that is a good remark. I forgot. I expect to get just a similar of your
example. And it will be in such a form that the value in field2, will
uniquely correspond to the value in field 1
Example:
(a) Field1: 68B | 69B | 70B |
(b) Field2: 1,2 | 1,2 | 1,5-8 |

This type of format will exactly be what I should get without the
parentheses for the second field and the unique value of field1 and field2
will be separated, from other unique values within the fields. For instance,
if Field1 is a Block and Field2 is a Lot all the values for field2 which is a
Lot that corresponds to field1 which is a block MUST be under the value they
corresponds to in the two fields. "70B must have under it 1,5-8";etc.

Thanks, I look forward for more reply

Zetony

 

[John Spencer]

Well, this is NOT good database design. The data seems to be storing
multiple values in a field (even after the change you desire). Good design
would be one piece of data per field and one row of data for each set of
data.

If you want the two fields to align you are going to have to use a
mono-spaced font to maintain the alignment.

Can you redesign the table structure or are you stuck with what you have?
Which version of Access are you running? That makes a difference in the VBA
that would have to be written to parse the current bad field into two bad
fields.

 

[Guest]

Hi John,
I have seen that you want to help me out. The fact is that I'm rewriting a
database from Lotus Application into MS_Access. This particular field has
been a problem because they originally combined the "lot" (numerics in
parentheses) and the "block" (numerics not in parentheses) in one field in
the table.

But I'm required to separate them based on the illustrations I gave from the
beginning. The record is in thousands, so creating a new field and transfer
them individually, you already know what that will mean. How do I get through
this "aching pain"?

 

[John Spencer]

Here is some untested code that might work for you. You would need to call
it from another VBA function that was stepping through your records.

Something like:
Dim strOne as Variant , strTwo as Variant
ParseTwo ExistingField, strOne, StrTwo
fldPartOne = strOne
fldPartTwo =strTwo

'UNTESTED Sub below
Sub parseTwo(ByVal strIN, ByRef strA, ByRef StrB)
Dim iLoop As Integer, iPos As Integer
Dim arCol1() As Variant
Dim arCol2() As Variant
Dim Delimiter As String: Delimiter = " * "
Dim strPad As String

If IsNull(strIN) Then
strA = ""
StrB = Null
Exit Function
End If

strA = vbNullString
StrB = vbNullString

'Start looking for pairs
While Len(strIN) > 0
ReDim Preserve arCol1(iLoop)
ReDim Preserve arCol2(iLoop)
iPos = InStr(1, strIN, "(", vbTextCompare)
If iPos = 0 Then iPos = Len(strIN) + 1
arCol1(iLoop) = Trim(Left(strIN, iPos - 1)) & ""
strIN = Mid(strIN, iPos + 1)

iPos = InStr(1, strIN, ")", vbTextCompare)
If iPos = 0 Then iPos = Len(strIN) + 1
arCol2(iLoop) = Trim(Left(strIN, iPos - 1)) & ""
strIN = Mid(strIN, iPos + 1)
iLoop = iLoop + 1
Wend

'Build strings
For iPos = 0 To UBound(arCol2)
iLoop = Len(arCol1(iPos))
If Len(arCol2(iPos)) > iLoop Then iLoop = Len(arCol2(iPos))
strPad = Space(iLoop)
strA = strA & Left(arCol1(iPos) & strPad, iLoop) & Delimiter
StrB = StrB & Left(arCol2(iPos) & strPad, iLoop) & Delimiter
Next iPos
strA = Delimiter & strA
StrB = Delimiter & StrB

End Sub

 

[Albert D.Kallal]

Here is the code logic that will get you started
(just paste the following code into a standard code module..and then save
it...

Then, put your cursor into the sub test..and hit f5 to run...


Public Sub test8()

Dim s As String
Dim c1 As New Collection
Dim c2 As New Collection
Dim i As Integer


s = "68B (1,2) 69B(1,2) 70B(1,5-8)"

Call MyParse(s, c1, c2)

For i = 1 To c1.Count

Debug.Print "First set value is " & c1(i)

Next i

For i = 1 To c2.Count

Debug.Print "2nd () values are is " & c2(i)

Next i

End Sub

Public Sub MyParse(strText As String, colN1 As Collection, colN2 As
Collection)


Dim v As Variant
Dim t2 As String
Dim i As Integer

Dim s1 As String


v = Split(strText, "(")

colN1.Add AllTrim(v(0))

For i = 1 To UBound(v, 1)

s1 = AllTrim(Split(v(i), ")")(0))
If s1 <> "" Then colN2.Add s1

s1 = AllTrim(Split(v(i), ")")(1))
If s1 <> "" Then colN1.Add s1
Next i


End Sub

As for what you do with the output..that is up to you....

I would just use a code loop..and write out the values to another table...

I would consider writing out the 1,2,4 values to separate fields..as then
you can process the data. (and, the same for the () ones).

 

[Guest]

Hi John,
I do appreciate all your efforts. I will try the code and see whether it
will work out well for me. I say Thanks.

Zetony

 

[Guest]

Hi Albert,
I'm grateful for the code you sent to me. I will go ahead and try it out
right now.
Thanks

Zetony