Select only matching characters in field1 to field2

[Kayo]

I have two fields:
UIC1 = W12345
UIC2 = 12345

How do select all the records in UIC1 That match the
five characters in UIC2?

V/r

 

[OnTheFly]

You will using a wild card to do this with the LIKE operator (more than likely) but to give you the exact syntax I will need more information.

I believe a little more information is in order to get you the correct answer.

First, are both fields in the same table or 2 different tables?

Are they both text fields?

Is UIC2 always going to be a single value or are you looking for all UIC1 that contain valid UIC2 values?

Are you looking to match the UIC2 string at any position in UIC1 or just the last characters of UIC1?

Hope this helps.

OnTheFly

 

[lespaul]

I know this works in standard SQL, not sure about access:

Select UIC1, UIC2 FROM tblName WHERE SUBSTRING(UIC1, 2, 5) = UIC2



Leslie

 

[Kayo]

Leslie, thanks for getting back so quickly on this. I tried to respond to you immediately yesterday but the system was experiencing problems. In answer to your questions of yesterday:
1. The fields are in different tables.
2. The fields are both text fields.
3. UIC2 will always be a single value. I'm looking all records of UIC1 that match UIC2 e.g.,

Table1: UIC1 SSN
W12345 987654321

UIC2 SSN
Table2: 12345 445665444

SELECT UIC1, SSN FROM TABLE1 WHERE UIC1 = '12345';

I know this is confusing, but it's the best I can describe what I'm trying to do.

V/r,

Gregg

 

[OnTheFly]

Try this

SELECT UIC1, SSN FROM Table1 WHERE UIC1 Like '*12345*'



Hope this helps.

OnTheFly