Help with removing characters.

[scgoodman]

I have a query that has multiple values in a row. i.e.
Delay Codes

qryTotalDaysTransitDLYBND, GAD, AD

I only want to show AD, and remove the remaining leading characters. The field could also be null. Any advice?

 

[Alansidman]

I thin you will find your answers here

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

Alan

 

[scgoodman]

This did not help...

I have potentially 3 sets of characters in a field and could be more.

i.e.
Delay Code Variations that could happen
DLY, BND, AD
BND, AD
AD
Null


I need to only pull AD and leave the rest out. Could be sometimes one delay code, or could be up to 5. Thoughts?

 

[raskew]

Hi -

Don't quite understand the logic behind your situation, but nonetheless this would isolate the desired characters, regardless of their position in the string.

From the debug (immediate) window:

x = "DLYBND, GAD, AD"
? mid(x, instr(x, "AD"), 2)
AD

HTH - Bob

 

[scgoodman]

No, sorry to be so difficult. I have many fields, here is a sample of the data.

1. BND, GAD, AD, AD
2. BND, GAD
3. AD, HO
4. Null

For example, I just want to see the last record on this dataset. Strip the front characters.
1. AD
2. GAD
3. HO
4. Null

 

[raskew]

Hi -

Not a problem. Try this (examples of multiple, single, null values shown):

x = "BND, GAD, AD, AD"
[B]? iif(instr(x, ",")> 0, trim(mid(x, instrrev(x, ",")+1)),x)[/B]
AD

x = "AD"
[B]? iif(instr(x, ",")> 0, trim(mid(x, instrrev(x, ",")+1)),x)[/B]
AD

x = ""
[B]? iif(instr(x, ",")> 0, trim(mid(x, instrrev(x, ",")+1)),x)[/B]

HTH - Bob

 

[scgoodman]

Awesome...that worked great. Thanks
Shannon

 

[raskew]

Great! Glad it works for you.

Best Wishes -- Bob