How to detect the same substring

[D]

Hi:

Can you please tell me how do I do the following:

I have a text field [dealno]; the maximum lenght for it is 9.
ieEALNO Pair Matured Leg 1
FX05875 P Yes
FX05876 P Yes
FX05877A Yes
FX05878B Yes
FX05879 P Yes
FX05879X P Yes L 1

The ones that are the same until X are 'pairs' and have P in Pair field; How
do I get not P for the others??
On the same note how do put L 2 in Leg field for the on with no X??

Below is my criteria that does not work 100%:

Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In
("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))

/****

Leg 1: IIf(Right([dealno],1)="X","L 1")

THANKS A LOT!!!

Dan

 

[John Spencer]

Somehow I don't see that your sample data matches the criteria you have
posted.

How are the first two items in your list the same? FX05875 and FX05876
match up to the last character.

Would you care to try to state in words what how record 1 and 2 are a pair?
And how the last two records are a pair? And why the middle two aren't a
pair?

Leg 1: IIf(Right([dealno],1)="X","L 1","L 2") puts L 1 where the last char
in DealNo is an X and L 2 in all other cases.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[D]

Hi John:

Thank you!

The records that are "pairs= because up to X are the same chars." are the
ones that until X have the same characters: FX05879 and FX05879X - all the
pairs are one with x and one without.
The P for the first two is there because my condition did not work; sorry!

The middle two are not pairs because do not have X and are different.

Thanks again,

Dan

Somehow I don't see that your sample data matches the criteria you have
posted.

How are the first two items in your list the same? FX05875 and FX05876
match up to the last character.

Would you care to try to state in words what how record 1 and 2 are a pair?
And how the last two records are a pair? And why the middle two aren't a
pair?

Leg 1: IIf(Right([dealno],1)="X","L 1","L 2") puts L 1 where the last char
in DealNo is an X and L 2 in all other cases.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi:

Can you please tell me how do I do the following:

I have a text field [dealno]; the maximum lenght for it is 9.
ieEALNO Pair Matured Leg 1
FX05875 P Yes
FX05876 P Yes
FX05877A Yes
FX05878B Yes
FX05879 P Yes
FX05879X P Yes L 1

The ones that are the same until X are 'pairs' and have P in Pair field;
How
do I get not P for the others??
On the same note how do put L 2 in Leg field for the on with no X??

Below is my criteria that does not work 100%:

Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In
("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))

/****

Leg 1: IIf(Right([dealno],1)="X","L 1")

THANKS A LOT!!!

Dan

 

[John W. Vinson]

Hi:

Can you please tell me how do I do the following:

I have a text field [dealno]; the maximum lenght for it is 9.
ieEALNO Pair Matured Leg 1
FX05875 P Yes
FX05876 P Yes
FX05877A Yes
FX05878B Yes
FX05879 P Yes
FX05879X P Yes L 1

The ones that are the same until X are 'pairs' and have P in Pair field; How
do I get not P for the others??
On the same note how do put L 2 in Leg field for the on with no X??

Below is my criteria that does not work 100%:

Pair: IIf(Right([dealno],1)="X","P",IIf(Mid([dealno],8,1) Not In
("A","B","X",Not Null),IIf(Left([dealno],8)<>"FX????X?","P")))

/****

Leg 1: IIf(Right([dealno],1)="X","L 1")

THANKS A LOT!!!

Dan

You may be able to find these pairs with a SELF JOIN query:

SELECT A.DEALNO, B.DEALNO
FROM yourtable AS A INNER JOIN yourtable AS B
ON B.DEALNO = A.DEALNO & "X";

This query will find all pairs (triples, quads, quints...) where one record
has a given dealno value and the other record has that same value with an X
appended to it.

John W. Vinson [MVP]

 

[John Spencer]

FX05875 P Yes
FX05876 P Yes
FX05877A Yes
FX05878B Yes
FX05879 P Yes
FX05879X P Yes L 1

So with that logiic, you have one pair in the above
FX05879 AND FX05879X
And
FX05875 and FX05876 are not a pair - even though your sample has a P in
what looks like the Pair column.

I think that John Vinson has posted the beginnings of a solution. Did you
understand what he has posted and can you use that to get the deals marked
with pairs?

SELECT A.DEALNO, B.DEALNO
FROM yourtable AS A INNER JOIN yourtable AS B
ON B.DEALNO = A.DEALNO & "X";
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

news:[email protected]...

 

[D]

Thanks a lot both of you!

It is a start; I have to find out how do I incorporate all the other fields.

Thanks,

Dan