Update Query: Breaking a Field into Several Fields

[jrattner]

Forums,

I have a field within a table entitled "Material Description". This field contains information about our material:

Example Value 1: 6-1/2X1.500-304LS
Example Value 2: 6X.250-304LS
Example Value 3: 4X.065-316LW

However, I need to convert this field into two separate fields entitled, "Group" and "size" based on the its content. For example, the correct conversion of the examples above would be:

Example 1:
Group: SMT
Size: 6.121.500

Example 2:
Group: SST
Size: 6.250

Example 3:


Group: SWT
Size: 4.065

Rules:
1. Group:

• If the description ends in a "s" and the portion after the X is greater than or equal to .188 then group = "SMT".
• If the description ends in a "s" and the portion after the X is less than .188 then group = "SST".
• If the description ends in a "W" then the group = "SWT".

2. Size: Numbers before the X need to be converted. For example, "1-1/4" becomes "1.14". But, the numbers after the X remain as is. The X disappears. The dash and all characters after it should be removed.

Please let me know if you have any ideas. Figuring out how to convert the size is most perplexing.

Thank you,

JR

[June7]

Use string manipulation and conditional structures.

The first break is fairly easy if the part at the end will always be 5 characters:
PartA = Left([fieldname],Len([fieldname])-6)

If there is always a single X, get the part that follows:
NumPart = Mid(PartA, Instr(PartA,"X")+1)

Group = "S" & IIf(Right([fieldname],1)="W","W", IIf(NumPart>=0.188,"M","S")) & "T"

Now the size part - don't want 1/4 to become .25?
Size = Replace(Replace(Replace(PartA,"-","."),"/",""),"X","")

You can nest the expressions for PartA and NumPart for some very long expressions in query or write custom function that is called in the query.