Compare Two Strings Problem (1 Viewer)

[DQuick]

Hey everyone,

I have an issue I have been working on for a day now and I cant seem to crack it.

I am trying to use wildcards to compare two strings of numbers.
One of the strings may have a few leading numbers.

Example:
Table 1: 92123456789
Table 2: 123456789


Sample code I'm using:

UPDATE [Table 1] LEFT JOIN [Table 2] ON [Table 1].[Tracking#] = [Table 2].[Tracking Number]
SET [Table 1].Description = "Shipment failed to reconcile"
WHERE ((([Table 1].[Tracking#]) Not In ([Table 2]![Tracking Number (Transaction)])));


This code works if they are identical, but it needs to search within a string.


Thanks in advance for any help.

 

[jdraw]

I suggest you work with a SELECT query first and get that working so that it identifies the records you want. Confirm it is correct. Then move to an UPDATE query.

What do table1 and table2 represent?

 

[DQuick]

I suggest you work with a SELECT query first and get that working so that it identifies the records you want. Confirm it is correct. Then move to an UPDATE query.

What do table1 and table2 represent?

The two fields I am attempting to compare are tracking numbers.
One company may only report the last 17 out of 22 digits, and others may report all 22. I thought about trying to trim off the excess digits, however, there is no set amount that it may be over.

 

[jdraw]

Why do you have 2 tracking numbers? How come you don't control the length of tracking number?
Might be easier for the reader if you told us what is being tracked and the processes/products involved.

 

[DQuick]

Why do you have 2 tracking numbers? How come you don't control the length of tracking number?
Might be easier for the reader if you told us what is being tracked and the processes/products involved.

I'm not sure how any of that would help. :banghead:

These are FedEx tracking numbers. We have no control over the length of the tracking numbers.
I've built a system to reconcile web orders vs FedEx EDI data. Our 750 vendors input the FedEx tracking number into a backend ordering tracking system.

So, now that you have a bit of background, do you have an answer?

 

[JHB]

Try the below, create a new query, switch to "SQL View", (and it can oly be viewed in "Sql View").

UPDATE [Table 1] INNER JOIN [Table 2] ON right([Table 1].[Tracking#],len([Table 2].[Tracking Number])) = [Table 2].[Tracking Number]
 SET [Table 1].Description = "Shipment failed to reconcile"
WHERE ((([Table 1].[Tracking#]) Not In ([Table 2]![Tracking Number (Transaction)])));

 

[DQuick]

Try the below, create a new query, switch to "SQL View", (and it can oly be viewed in "Sql View").

UPDATE [Table 1] INNER JOIN [Table 2] ON right([Table 1].[Tracking#],len([Table 2].[Tracking Number])) = [Table 2].[Tracking Number]
 SET [Table 1].Description = "Shipment failed to reconcile"
WHERE ((([Table 1].[Tracking#]) Not In ([Table 2]![Tracking Number (Transaction)])));

That still didnt work.

Lets make this simple.
I've created a new database just to work this out.

Table1
Tracking Number
123456789
741852963
789456123
963852741
987654321
888887457

Table2
Tracking Number
1111123456789
2811741258963
9411789456123
2311963258741
7111987654321


Here is the sample code
SELECT Table1.[Tracking Number]
FROM Table1 LEFT JOIN Table2 ON Table1.[Tracking Number] = Table2.[Tracking Number]
WHERE (((Table1.[Tracking Number]) Not In ([Table2].[Tracking Number])));

This code only works if the string is identical.
I need this query to only return "888887457".


Thanks again for any help!

 

[CJ_London]

try

SELECT Table1.[Tracking Number]
FROM Table1
WHERE NOT Exists(SELECT * FROM Table2 as Tmp WHERE instr([Tracking Number],Table1.[Tracking Number)=0)

 

[CJ_London]

Actually, what would be better

SELECT Table1.[Tracking Number]
FROM Table1
WHERE NOT Exists(SELECT * FROM Table2 as Tmp WHERE right([Tracking Number],len(Table1.[Tracking Number]))=Table1.[Tracking Number])

 

[MSAccessRookie]

Based on the Prefixes contained in Table2, I suspect that you could NEVER find Table1.[Tracking Number] in the list of Table2.[Tracking Number]s. I think that jhb was on the right track by suggesting use of the Right() Function to locate the entries in Table2.[Tracking Number] which contain Table1.[Tracking Number]. I would add that the WHERE Clause might also needs to locate the entries in Table2.[Tracking Number] which contain Table1.[Tracking Number].

-- Rookie

WOW! CJ London completed two posts completed while I was working on mine. As a matter of point, it looks like the second post is more accurate for your needs, since it gets the rightmost characters only, and will not return a false positive if a prefix creates an unintended match at the beginning or in the middle.

 

[Libre]

I don't know how to set up a query to do this. As you say, the strings would have to be equal. Almost equal, or partly equal is not equal.
But I know how to do it in VBA. The Like operator.
I would create a VBA procedure with a nested loop to loop thru table 1 and compare each tracking number with each tracking number in table 2 using a comparison such as:

If sTrackingNo1 Like "*" & sTrackingNo2 & "*" Then
     bNumbersMatch = True
End If

where sTrackingNo1 and sTrackingNo2 are string variables and bNumbersMatch is a boolean.
The code above should trap any sTrackingNo1 that is WHOLLY contained in sTrackingNo2. If that is the case then bNumbersMatch = true and then the procedure can write both numbers to a line in a new table.

Don't know if this is clear or if it is what you are after. I started to write the whole procedure but not going to put the time in because:
1- I'm at work myself;
2- May not be what you need.

 

[DQuick]

Actually, what would be better

SELECT Table1.[Tracking Number]
FROM Table1
WHERE NOT Exists(SELECT * FROM Table2 as Tmp WHERE right([Tracking Number],len(Table1.[Tracking Number]))=Table1.[Tracking Number])

You're my hero!


That worked perfectly


*I was able to get it working in the actual enviroment, its slow but it works.