Queries Field Join of URL not working

[Arnold]

I need to query by join 2 tables
Table A has 150 records
Table B 500 records
The join is the column "URL" and only records that are equal.


For some reason access only recognises one group of urls and does not join the other urls even though they are identical and match in excel.
Here are the example of a URl that match and one that does not.

Joined (notice the extra long url)
http://www.walmart.com/ip/Corelle-Li...client_guid=ca

Not joined


http://www.walmart.com/ip/Cosco-Spri...ntiki/34448695

Any solutions?

[ItsMe]

I can't see the entire URL but your issue may be related to a >250 characters thing.

[Arnold]

Thank you
The problem is that it recognizes the long url and not the short one.

[ItsMe]

Literal text is literal text. Perhaps you are joining on a field that is of type Hyperlink. Maybe the literal text of the two hyperlink fields are not equal but, still, they appear equal to humans.

[Arnold]

They are both short text.

[ItsMe]

All I can suggest is to post your SQL. However, you mentioned it works for some while it does not work for other records. So, I doubt there is a syntax issue with the SQL.

[Arnold]

Thank you
I will see if there may be another suggestion.

[Arnold]

I see that the join works only on values of 255 characters (max amount) any urls that are less even by 1 character aren't joining.
Does anyone have a clue why this would be?

Thanks in advance

[June7]

Did you mean 255 or greater? That does seem odd but I've never tried to join on URLs.

What field type are these two fields?

A hyperlink type field holds a hyperlink value that is composed of 3 parts. http://www.allenbrowne.com/casu-09.html

[Arnold]

Thank you for your response.
It isn't set as hyperlink. Its a text field. I believe its exactly 255.

[June7]

A regular text field is limited to 255 characters, a memo type is much larger (doesn't Access 2013 call them short text and long text?). I think the length limit for URL is about 2,048.

Joins cannot be done on memo fields. So the behavior you describe is very odd. I cannot understand why joins on less than 255 characters would fail.

[Arnold]

Would it help to upload a sample of what we are talking about?

[June7]

Would not hurt.

[Arnold]

Here is the table thanks

[June7]

Table CombineLists has 7155 records
Table Competitors has 5404 records

Query1 does not return any records
SELECT CombineLists.AmznURL_Cl, Competitors.URL
FROM Competitors INNER JOIN CombineLists ON Competitors.URL = CombineLists.AmznURL_Cl;

Query2 returns 292 records
SELECT CombineLists.Supplier1URL, Competitors.URL
FROM Competitors INNER JOIN CombineLists ON Competitors.URL = CombineLists.Supplier1URL;

Lengths of these URLs are 255 characters and I expect have been truncated from even longer strings. These links probably won't even work.

So I did queries to return all records with URL <255. There are 7099 in CombineLists and 5249 in Competitors. Then I compared them side by side, sorted ascending. Definitely see same URLs in both. Joined these two queries and no records show.

When I use cursor down movement to navigate the URL field in Competitors, there appears to be extra space captured at the end of the URL. This does not show in the CombineLists table fields. The join for URLs of 255 characters sort of works. The 'extra' space is beyond the 255th and has been truncated off because of the 255 character limit for text field. I say sort of works because all URLs the same up to 255 characters will join each other.

However, when I click/sweep select string in URL, the end 'space' does not appear selected. There is some sort of non-printing, non-selectable character at the end of these strings. I ran an UPDATE action to remove the phantom character.
UPDATE Competitors SET Competitors.URL = Left([URL],Len([URL])-1);

Now Query2 returns 7093 records. No idea if any are duplicates.