Group based on Substring value

[mdlaugh1]

Hello,
I have a table with a Description field that contains data as follows: (there is a carriage return before each "Class...." string.
---------------
Mary Heads Carter Park
Class "A"

Josey Ranch Greenbelt
Class "B"
-------------
I want to create a new column containing the substring 'Class "A"', 'Class "B"', etc... then I will Group on this new column.

How do I substring the Description field to pull just the Class... piece?

thank you!!

 

[Remou]

You can use Instr to get the position of 'Class':

[tt]Mid([Description],Instr([Description],"Class"),7)[/tt]

 

[mdlaugh1]

Hi Remou,

thank you!
That is working perfectly for all records that contain the "Class..." and theoretically all records should, however there are a few that are missing this text.

I tried the following to test for existence of "Class..." string first, but now all rows return blank in the column. What have I missed? I don't understand the code portion of the IIF... > 0. I pulled this tip from another Post related to Substring...


IIf((InStr(Asset_Desc, "Class") > 0), Mid([Asset_Desc],InStr([Asset_Desc],"Class"),9),' ')

 

[Remou]

Make sure that in the test for 'class' you are looking at the field Asset_Desc, not the string "Asset_Desc", and watch out for nulls:

[tt]IIf(Nz(InStr([Asset_Desc], "Class"),0) > 0, Mid([Asset_Desc],InStr([Asset_Desc],"Class"),9)," ")[/tt]