Test with (D) or (D1) or (D2) or (DD) - Discontinued

[Gary F Shelton]

I am trying to write a query to help me identify if a sku is discontinued.
The text field from our mainframe comes across as:

1. HAR 4/2.5 BATTERED CHEDDAR CRISPS
2. (D)GNR 8/2 3/16" BATTERED ONION RINGS
3. (D1)DMN 1/8 SPRINGROLL - CHICKEN
4. (D2)MCX 9/2.5 BBAT ONI HOT SCOOPERS
5. (DD)MCX 10/1kg BATTERED ONION RINGS

The statement I wrote is:
Active_Inactive: IIf(Left(Trim([Product
Description]),3)="(D)","D",="(D1","D1",="(D2","D2",="(DD","DD","Active")

As you can imagine I got an error.

 

[Clifford Bass]

Hi Gary,

Assuming that all items that start with "(D" are discontinued and that
they have a closing paranthesis, try:

IIf(Left([Product Description],2)="(D",Left([Product
Description],InStr([Product Description],")")),"Active")

Clifford Bass

 

[Beetle]

You can return what is between the parentheses with;

IIf(InStr([Product Description],"(")>0,Mid([Product
Description],2,InStr([Product Description],")")-2),"Active")

This assumes there will be no other occurrences of parentheses in the text.

 

[Douglas J. Steele]

You need to repeat what it is you're checking:

Active_Inactive: IIf(Left(Trim([Product
Description]),3)="(D)","D",Left(Trim([Product
Description]),3)="(D1","D1",Left(Trim([Product
Description]),3)="(D2","D2",Left(Trim([Product
Description]),3)="(DD","DD","Active")