如何比较SQL语句中OUTER JOIN ON子句中文本/字符串的整数值？

I would like to execute the SQL statement below in VBA code written for MS Access.

我想在为MS Access编写的VBA代码中执行下面的SQL语句。

I want the integer values of the ID fields to be compared in the JOIN ON criteria.

我希望在JOIN ON条件中比较ID字段的整数值。

I get an error when using CInt(). I've also tried Val(), CAST(), and Convert().

使用CInt()时出错。我也尝试过Val(),CAST()和Convert()。

Set RecordSet = "SELECT A.id, B.id 
FROM A LEFT OUTER JOIN B ON CInt(A.id)=CInt(B.id)"

2 个解决方案

#1

Lillyana the best would be do the outer join query in MS ACCESS and then go the sql view.

Lillyana最好的是在MS ACCESS中进行外连接查询,然后进入sql视图。

• MS ACCESS outer join

MS ACCESS外连接

Then as for Nz function: It will return string value (custom defined string value) when the value of your columnn is null

然后对于Nz函数:当你的columnn的值为null时,它将返回字符串值(自定义字符串值)

• Nz

Usual case for the error you are getting is due to,

通常情况下你得到的错误是由于,

• The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. (Error 3258).

无法执行SQL语句,因为它包含不明确的外连接。要强制首先执行其中一个连接,请创建一个执行第一个连接的单独查询,然后在SQL语句中包含该查询。 (错误3258)。

#2

Assuming all your id fields are indeed integers, CInt() should work fine. If your numbers are larger, try CLng():

假设你的所有id字段都是整数,CInt()应该可以正常工作。如果您的数字较大,请尝试CLng():

SELECT A.id, B.id 
FROM A 
   INNER JOIN B ON CLng(A.id)=CLng(B.id)