parse text field

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi, I have a text field in the manner of:

1-90
1-95A
1-95C
3-150
3-150A
7-555
8-27D

First character to the left of the dash will be 1-9. The next set of numbers
after the dash can be anywhere from 1-700. There might or might not be a
one-character Text at the end. I need to break these out, for sorting
purposes.

I can get the first character ok (easy enough), but not having much luck with
the second two 'sets'.

Suggestions?
Thanks
 
Hi, I have a text field in the manner of:

1-90
1-95A
1-95C
3-150
3-150A
7-555
8-27D

First character to the left of the dash will be 1-9. The next set of numbers
after the dash can be anywhere from 1-700. There might or might not be a
one-character Text at the end. I need to break these out, for sorting
purposes.

I can get the first character ok (easy enough), but not having much luck with
the second two 'sets'.

Suggestions?
Thanks
Click to expand...

In a query?
FirstPart: Left([CombinedValue],1)

SecondPart: Val(Mid([CombinedValue],3))

ThirdPart:
IIf(Asc(Right([CombinedValue],1))>=65,Right([CombinedValue],1),"")
 
Hi, I have a text field in the manner of:

1-90
1-95A
1-95C
3-150
3-150A
7-555
8-27D

First character to the left of the dash will be 1-9. The next set of numbers
after the dash can be anywhere from 1-700. There might or might not be a
one-character Text at the end. I need to break these out, for sorting
purposes.

I can get the first character ok (easy enough), but not having much luck with
the second two 'sets'.
Click to expand...

Well, you're paying the penalty for storing three disparate pieces of
information in one field. This should actually be stored in THREE fields which
could be concatenated on demand - maybe that's what you're trying to do!

Naming the fields Num1, Num2 and Suffix, try:

Num1 - Left([field], 1) < I presume you have this>

Num2 - Val([field], 3)

Suffix - Iif(IsNumeric(Right([Field], 1), Null, Right([Field], 1))
 
Hi, I have a text field in the manner of:

1-90
1-95A
1-95C
3-150
3-150A
7-555
8-27D

First character to the left of the dash will be 1-9. The next set of numbers
after the dash can be anywhere from 1-700. There might or might not be a
one-character Text at the end. I need to break these out, for sorting
purposes.

I can get the first character ok (easy enough), but not having much luck with
the second two 'sets'.

Suggestions?
Thanks
Click to expand...

In a query?
FirstPart: Left([CombinedValue],1)

SecondPart: Val(Mid([CombinedValue],3))

ThirdPart:
IIf(Asc(Right([CombinedValue],1))>=65,Right([CombinedValue],1),"")
Click to expand...

Works fine, I had to lookup Asc to see why the >=65, but that taught me
something. ("A"=65)

Thanks.
 
Hi, I have a text field in the manner of:

1-90
1-95A
1-95C
3-150
3-150A
7-555
8-27D

First character to the left of the dash will be 1-9. The next set of numbers
after the dash can be anywhere from 1-700. There might or might not be a
one-character Text at the end. I need to break these out, for sorting
purposes.

I can get the first character ok (easy enough), but not having much luck with
the second two 'sets'.
Click to expand...

Well, you're paying the penalty for storing three disparate pieces of
information in one field. This should actually be stored in THREE fields which
could be concatenated on demand - maybe that's what you're trying to do!

Naming the fields Num1, Num2 and Suffix, try:

Num1 - Left([field], 1) < I presume you have this>

Num2 - Val([field], 3)

Suffix - Iif(IsNumeric(Right([Field], 1), Null, Right([Field], 1))
Click to expand...

Num2 - Val([field], 3) didn't work for me, got "...wrong number of arguements"
although putting fredg's Mid in works.

Suffix, works fine, after changing to:
Iif(IsNumeric(Right([Field], 1)), Null, Right([Field], 1))

Reference your statement about three disparate pieces of information, this was
imported from a DOS program, which DID have it in three different fields. I put
them together because its a primary key (account number field), related to
fields in at least two other tables. I know can have multiple fields make up a
primary key, but I thought 3 fields was too many........? For whatever reason,
I thought 2 fields would be max? Would there be a problem in having 3
fields as primary/foreign keys? I also thought there would be problems in, for
example, using a combo box to find the record based on the account number.

Thanks
 
Reference your statement about three disparate pieces of information, this was
imported from a DOS program, which DID have it in three different fields. I put
them together because its a primary key (account number field), related to
fields in at least two other tables. I know can have multiple fields make up a
primary key, but I thought 3 fields was too many........? For whatever reason,
I thought 2 fields would be max?
Click to expand...

The maximum number of fields in a primary key is actually *ten*.
Would there be a problem in having 3
fields as primary/foreign keys?
Click to expand...

Some problems, not unsuperable.
I also thought there would be problems in, for
example, using a combo box to find the record based on the account number.
Click to expand...

Makes it more complicated - you would want to have the concatenation as a
calculated field as the first field in the combo's rowsource, and would need
code to use the three fields (from the combo's Columns() property) to find the
record.

It might be a good idea to add a surrogate key - an Autonumber perhaps - as
the primary key of the table; put a unique Index on the three fields to ensure
that they aren't duplicated.
 
Back
Top