Re: Parsing Data

From: Rob (Rob_at_discussions.microsoft.com)
Date: 01/30/05 

Date: Sun, 30 Jan 2005 10:49:02 -0800

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" wrote:

> 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" <Rob@discussions.microsoft.com> wrote in message
> news:75A42B3B-9CBB-41B0-A692-4465708490AA@microsoft.com...
> > 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
>
>
>