Parsing Data

[Rob]

Hello

I've looked at all the subjects on this forum page and I cant seem to find
an answer to my question.

I need to parse the following data samples:

Current format Desired format (2 fields)

2003.01DC DC 2003.01
142.05DC DC 152.05
DC3323.00 DC 3323.00
DX38393.02 DX 38393.02
323.00DF DF 323.00

The numeric length format is variable as it relates to value. I tried some
of the samples from this forum using the Instr() but wont work. I must be
doing something wrong.

Any ideas? Will appreciate it!

Thanks
Rob

 

[Dan Artuso]

Hi,
Here's a routine that parses it:

Public Sub ParseData(strIn As String)
Dim i As Integer
Dim numPart As String
Dim dblNum As Double
Dim strPart As String

For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Or Mid(strIn, i, 1) = "." Then
numPart = numPart & Mid(strIn, i, 1)
Else
strPart = strPart & Mid(strIn, i, 1)
End If
Next i

dblNum = CDbl(numPart)
MsgBox "double: " & dblNum
MsgBox "string: " & strPart

End Sub

I guess what you would want to do is make two seperate functions, one to return the string part and
one to return the double part. You can test the above sub from the Immediate window.

 

[Rob]

Hi Dan

Thanks for your response.

Unfortunately, Im not a VB guy. Is there a way for me to write your code in
a query?

Thanks much!

Rob

 

['69 Camaro]

Hi, Rob.

If I understand your situation correctly, you have a table with multi-values
in one text field and you'd like to normalize this table by replacing the
multi-valued field with two separate fields. It would be easiest to do this
with VBA, but if that's not your strong suit, then it's still possible with
queries.

Here's an example of how to do this while using the following table
structure:

Table name: tblCodes
Field name: ID (AutoNumber), Primary key
Field name: Code (Text) <-- Contains both numeric and string data

Add two new text fields to the table, one named Nums to hold the numeric
values, and one named Chars to hold the text strings. The following SQL
statement will work to parse the field for you if and only if the value in
the Code field always contains either two characters before the numeric
value or two characters after the numeric value, and the numeric value
always has at least one digit followed by a decimal and two digits.

UPDATE tblCodes
SET Nums = IIF((IsNumeric(Left(Code, 2)) = FALSE), Right(Code, Len(Code) -
2), Val(Nz(Left(Code, Len(Code) - 2), 0))),
Chars = IIF((Val(Nz(Code, 0)) = 0) AND (IsNumeric(Right(Code, 2)) = TRUE),
Left(Code, 2), Right(Code, 2));

This will place the numeric values in the Nums field but will not show zeros
as place holders for tenths and hundredths (ex. 323, not 323.00). If this
is okay, then leave it as is. If the value must be in a number field, then
change the Nums field data type from Text to Single or Double and remove the
default value of zero. If the value must remain as a Text data type and
display the tenths and hundreds even if they are zeros, then the following
SQL statement will remedy this:

UPDATE tblCodes
SET Nums = Format(Nums, "0.00");

When you are satisfied that everything worked correctly, delete the
multi-valued Code field from the table, compact/repair the database, and
you're done.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Rob]

Hello Gunny,

Thanks so much for trying to help me with my subject matter.

I tried your queries below and it works well. However, I just noticed that
some of my data doesnt have decimals(.), so the NUMS came out truncated ie
$309 came out as 3.

Also, when I ran the Update query, decimal place holders didnt come up. I
tried changing the tabl format manually by going to Design view and changed
the format to Double and 2 decimal and still didnt do anything

Any suggestions?

Thanks !

Rob

 

['69 Camaro]

Hi, Rob.

I just noticed that
some of my data doesnt have decimals(.), so the NUMS came out truncated ie
$309 came out as 3.

And the Chars field holds "09" as well. That's why I cautioned: "The
following SQL statement will work to parse the field for you
_if_and_only_if_ the value in the Code field always contains either two
characters before the numeric value or two characters after the numeric
value, and the numeric value always has at least one digit followed by a
decimal and two digits." It doesn't work for other cases, just patterns
matching the descriptions of the string structure you gave as examples.

So $309 lacks the decimal and the two digits after the decimal, as well as
the two characters to match the pattern. After running the first update
query, run this next query to fix the records that don't match the common
pattern:

UPDATE tblCodes
SET Nums = IIF((IsNumeric(Chars) = TRUE), Code, Nums),
Chars = IIF((IsNumeric(Chars) = TRUE), NULL, Chars);

Also, when I ran the Update query, decimal place holders didnt come up.

That's why I offered the second update query. It formats the number string
so that the decimal, tenths and hundredths will all display as you expect.
However, they'll also be stored in the table that way, too, which isn't
nearly as efficient as storing numbers themselves. No matter. It sounds
like you won't be storing these digits in a Text field anyway, so you don't
need the second query.

I
tried changing the tabl format manually by going to Design view and changed
the format to Double and 2 decimal and still didnt do anything

Numbers don't show any placeholders after the decimal, because there aren't
any for whole numbers. Queries and forms can be customized so that these
placeholders can be displayed for the user, though. However, the Currency
data type will show the placeholders you are expecting, along with a dollar
sign. You could convert the Nums field from Text to the Currency data type,
but only after running both the first update query I gave you and the above
update query.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Dan Artuso]

Hi,
Okay, here are the two functions:

Public Function ParseNumber(strIn As String) As Double
Dim i As Integer
Dim numPart As String

For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Or Mid(strIn, i, 1) = "." Then
numPart = numPart & Mid(strIn, i, 1)
End If
Next i
ParseData = CDbl(numPart)
End Function

Public Function ParseString(strIn As String) As String
Dim i As Integer
Dim strPart As String

For i = 1 To Len(strIn)
If Not IsNumeric(Mid(strIn, i, 1)) And Mid(strIn, i, 1) <> "." Then
strPart = strPart & Mid(strIn, i, 1)
End If
Next i
ParseString = strPart
End Function

Simply call them from your query:

StringField: ParseString(yourField)

AND

NumField: ParseNumber(yourField)

 

[Rob]

Hi Gunny and Dan,

Thank you very much for your assistance !!!

Take care guys !

Rob