尝试使用带有字段中列表的like语句联接Access表 [英] Trying to join Access tables with like statement with list in field

问题描述

我有一个问题，我一直在寻找解决方案，但无济于事. 基础知识是，我试图通过将表1的字段中的值与表2的字段进行比较来连接Access中的2个表，表2的字段包含以列表类型格式连接的数字和其他几个数字. (两个字段均为文本类型)

I have a problem that I have been hunting for a solution to, but to avail. The basics are that I am trying to join 2 tables in Access by comparing a value in a field of Table 1 to a field in Table 2 that contains the number concatenated along with a few others in a list type format. (both fields are text type)

示例.
表1.CWT值= 640242
我要匹配的对应Table2.TAG_NO值= 640242; 635894; 058426

Example.
Table1.CWT value = 640242
Corresponding Table2.TAG_NO value I want to match to = 640242; 635894; 058426

以便它根据公用值(在这种情况下为640242)链接两个表.
到目前为止，我已经尝试了以下方法:

So that it links the two tables based on the common value (640242 in this case).
So far, I have tried the following:

左联接[Table2] ON [Table1].CWT类似于'*'& [Table2].TAG_NO& '*'

LEFT JOIN [Table2] ON [Table1].CWT like '*' & [Table2].TAG_NO & '*'

和

左联接[表2]开启[表1]. '*'，例如[Table2] .TAG_NO

LEFT JOIN [Table2] ON [Table1].CWT & '*' like [Table2].TAG_NO

以及这之间的每一个变化，我什至尝试使用％代替*.但是什么都行不通.在某些情况下，该值将是字符串中的第二个或第三个元素(在上面的示例中为635894)，因此我正在寻找一个在所有情况下均适用的选项.这类似于寻找CONTAINS函数的等效项，但似乎也不存在.
有人可以帮我吗?
谢谢
特德

and what seems like every variation in between, I have even tried using % instead of *. But nothing works. In some cases, the value will be the second or third element in the string (635894 in above example), so I am looking for an option that will work in all cases. This is akin to looking for the equivalent of the CONTAINS function, but that does not seem to exist either.
Can anyone help me out?
Thanks
Ted

推荐答案

您可以使用Instr函数来测试其他字符串中是否存在一个字符串，如下所示:

You can use the Instr Function that tests if a string exists in other string as below:

Select [Table1].CWT, [Table1].OtherColumn, [Table2].Column1Needed,[Table2].Column2Needed  
From [Table1], [Table2]
Where Instr([Table2].TAG_NO,[Table1].CWT)>0

请参见http://www. techonthenet.com/access/functions/string/instr.php

这篇关于尝试使用带有字段中列表的like语句联接Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！