加入具有不同数据类型的两个表 MS ACCESS - 'type mismatch in expression' 错误 [英] Joining Two Tables with Different Data types MS ACCESS - 'type mismatch in expression' error

问题描述

我正在尝试使用两个实时 CSV 对访问运行查询，这些 CSV 具有具有不同数据类型(数字和短文本)的公共字段.我发现您可以使用CStr"连接不同的数据类型.我已在 sql 视图中将CStr"添加到我的代码中.请找到请参阅下面的代码.

I'm trying to run a query on access using two live CSVs which has a common field with different data types(numbers and short text). I've discovered that you can join different data types using 'CStr'. I've added the 'CStr' to my code on the sql view. Please find the see the code below.

这给了我想要访问的输出，现在我可以在单击数据表视图"时看到输出.但是，当我尝试将数据导出(我实际上是在尝试创建导出规范，以便我可以使用宏导出 csv)作为 csv 时，我收到表达式中的类型不匹配"错误消息.

This gives me the output i want on access and i can now see the output when i click on 'datasheet view'. However, when i try to export the data (i'm actually trying create a export specification so that i can export a csv using macro) as a csv i'm getting a 'type mismatch in expression' error message.

这是我的代码:

SELECT Sixthform_Reg_Year_Groups.Forename, 
       Sixthform_Reg_Year_Groups.Surname, 
       Sixthform_Reg_Year_Groups.Reg, Students.objectGUID
FROM Sixthform_Reg_Year_Groups INNER JOIN
     Students
     ON CStr(Sixthform_Reg_Year_Groups.Person_id) = Students.employeeID
WHERE (((Sixthform_Reg_Year_Groups.Reg)="12E"));`

我也尝试在两侧添加CStr".如下，但遇到同样的问题.

I've also tried adding 'CStr' on both sides. as below, but experiencing the same issue.

FROM Sixthform_Reg_Year_Groups INNER JOIN
     Students
     ON CStr(Sixthform_Reg_Year_Groups.Person_id) = CStr (Students.employeeID)
WHERE (((Sixthform_Reg_Year_Groups.Reg) = "12E"));`

当然，如果没有CStr"，我什至无法在数据表视图"上查看输出.每次单击数据表视图时，它都会给我表达式中的类型不匹配"错误消息.

And of course, without 'CStr' i can't even view the output on 'datasheet view'. Every time when i click on datasheet view it's giving me the 'type mismatch in expression' error message.

对解决此问题的任何帮助将不胜感激.

Any help in resolving this would be much appreciated.

提前致谢.

附加信息:数据类型是 EmpoyeeID 是Short Text"，而 Person ID 是Number"

Additinal info: the data types are EmpoyeeID is 'Short Text' and Person ID is 'Number'

推荐答案

好的，我已经设法解决了这个问题，最后结果很简单.这就是我所做的.

OK guys, I've managed to resolve this issue, It turned out to be simple in the end. This is what i did.

基本上，我重新导入了链接表.这次在导入窗口中，我单击了高级"并将人员 ID"列上的数据类型更改为短文本"以匹配员工 ID"数据类型.然后所有的问题都解决了.(我不知道你可以这样做)

Basically, I re imported the linked table. This time on the import window i clicked on 'advanced' and changed the data type to 'short text' on the 'person ID' column to match with the 'employeeID' data type. And then all problems solved. (I didn't know you could do this)

谢谢大家的回复.浏览您的评论引导我走上正确的道路.

Thank you all for your replies. Going through your comments guided me to the right path.

非常感谢.

这篇关于加入具有不同数据类型的两个表 MS ACCESS - 'type mismatch in expression' 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！