Splitting values in a field based on a space

[ChuckW]

Hi,

I have a field called PartNumber that has values that are either A3452A or
A2958B 030, or b21-A3982A 34A. What I want to do is to create a query that
will only take the first grouping of alphanumeric characters and drop
everything after it (if anything exists) such as the space followed by 030 or
34A. A query that simply creates two separate fields is fine. Can someone
help?

Thanks,

 

[S.Clark]

Given: [MyField] = "b21-A3982A 34A"

NewField: IIF(InStr([MyField]," ")>0,Left$([MyField], InStr([MyField],"
")-1),[MyField])

 

[John W. Vinson]

Hi,

I have a field called PartNumber that has values that are either A3452A or
A2958B 030, or b21-A3982A 34A. What I want to do is to create a query that
will only take the first grouping of alphanumeric characters and drop
everything after it (if anything exists) such as the space followed by 030 or
34A. A query that simply creates two separate fields is fine. Can someone
help?

Thanks,

You can use the builtin string handling function InStr in conjunction with the
Left() substring function. To handle cases with no blank, add your own extra
blank:

Left([PartNo], InStr([PartNo] & " "), " ") -1)