SQL选择查询不工作。在2010年访问女士

here are my two charts and my query. Obviously you do see that some parameter names match. but my query shows up as no results. WHY IS THAT?

这是我的两个图表和我的查询。显然，您可以看到一些参数名称匹配。但我的查询显示为无结果。这是为什么呢?

Thank you .

谢谢你！

Altho this query was made in access 2010 it also works in 2003 and 2007

这个查询是在access 2010中进行的，它在2003年和2007年也是有效的

if you cant read the query in detail. just zoom in on this webpage

如果您不能详细阅读查询。放大这个网页

UPDATEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE: ****************************8

UPDATEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE:* * * * * * * * * * * * * * * * * * * * * * * * * * * * 8

thanks for all your help but i have solved it my own way. and because im doing this for work i needed it to be done fast and didnt have too much time to dela with why access trim does nto work =( ... i still dunno why.

谢谢你的帮助，但我已经用自己的方式解决了。因为我这样做是为了工作，所以我需要快速地完成工作，并且没有太多的时间去探究为什么access trim会工作=(……我仍然不知道为什么。

but altho my source table is big that one did not have trialing spaces. only the parameter table which i had uploaded from another table that was an excel file and 99 rows.

但是我的源表很大，一个没有测试空间。只有我从另一个表上传的参数表是excel文件和99行。

i went back into the orignal excel file. tried a trim there. copy pasted the trimmed column and paste special values to rid of the formula and it still has the space!! is this jsut an error on the trim function or what?

我回到orignal excel文件。尝试了削减。复制粘贴裁剪的列，粘贴特殊的值以删除公式，它仍然有空格!!这个jsut是修剪功能上的错误还是什么?

anyways instead i used a =left(cell,len(cell)-1) to rid of the one trailign space

不管怎样，我使用了一个=left(cell,len(cell)-1)来去掉一个trailign空间。

then reuploaded the excel parameter table and now its working. lengthy story but. if anyone has another suggestions on why TRIM doesnt work please explain . i used LTRIM RTRIM. BOTH together and jsut TRIM

然后重新上传excel参数表，现在可以工作了。但冗长的故事。如果有人对为什么修剪不起作用有其他建议，请解释。我用LTRIM空白。一起和jsut装饰。

5 个解决方案

#1

In a reply to @flayto you said "*turns out there is one trailing space behind each Parameter_Name value in ParameterTable. But i tried trimming in it does say it updates all of the entries in that table yet the one space sitll does not go away!*"

在对@flayto的回复中，您说“*原来在参数表中每个参数名值后面有一个尾随空格。但是我试着把它剪下来它确实说它更新了表格中的所有条目但是一个空间情景不会消失!

A few times I have copied a range of cells from an Excel spreadsheet into Access tables. The data in Access included invisible trailing characters after the text I expected. Since those characters were not spaces, they were not removed by the Trim() function. See what you actually have following the Paramater_Name values.

有几次，我将Excel电子表格中的一系列单元格复制到访问表中。访问的数据包括在我所期望的文本之后的不可见的尾随字符。由于这些字符不是空格，所以不会被Trim()函数删除。查看在Paramater_Name值之后的实际内容。

SELECT
    Parameter_Name,
    Len(Parameter_Name) AS Length_of_Parameter_Name
    Asc(Right(Parameter_Name, 1)) AS rightmost_character
FROM ParameterTable;

The fix will depend on what you find. For example, if there is always one and only one unwanted character, you could discard it with this UPDATE statement:

修复将取决于您找到了什么。例如，如果总是有一个且只有一个不需要的字符，您可以使用以下更新语句丢弃它:

UPDATE Parameter_Table
SET Parameter_Name = Left(Parameter_Name, Len(Parameter_Name) -1);

Whatever UPDATE you try, make sure to backup the database first in case anything goes wrong.

无论您尝试什么更新，请确保首先备份数据库，以防发生任何错误。

#2

Are you sure there are no leading or trailing spaces in the field values? Try wrapping each field with LTRIM/RTRIM as in LTRIM(RTRIM(T1_SourceTable.Parameter_Name)) just to be sure that isn't the problem.

您确定字段值中没有前导或尾随空格吗?尝试使用LTRIM/RTRIM (RTRIM(T1_SourceTable.Parameter_Name))包装每个字段，以确保这不是问题所在。

#3

Because you're comparing ParameterTable.Full_Name to T1_SourceTable.Parameter_Name.

因为你比较ParameterTable。Full_Name T1_SourceTable.Parameter_Name。

You should be comparing ParameterTable.Parameter_Name to T1_SourceTable.Parameter_Name.

您应该比较参数表。Parameter_Name T1_SourceTable.Parameter_Name。

***NOTE: I'm leaving this up while I look at this, but I'm suspecting the leading/trailing spaces (as mentioned by a couple of others).

***注意:在查看这个时，我将把它保留下来，但我怀疑是前导/后置空格(如其他几个人提到的)。

***Possible other solution: Modify this into a specific Inner Join. The links I'm finding from MS don't specifically you can't do what you've done, but they do all use the explicit INNER JOIN Syntax.

***可能的其他解决方案:将其修改为特定的内部连接。我从MS中找到的链接并不是你做不到的，但是它们都使用了显式的内部连接语法。

So you might try:

所以你可以试一试:

SELECT T1.Full_Name, T2.Parameter_Name
FROM ParameterTable T1
INNER JOIN T1_SourceTable T2
  ON T1.Parameter_Name = T2.Parameter_Name

This shouldn't, as far as I know, actually change anything- but, based on the links I'm finding, you might try it out.

据我所知，这不会改变任何东西——但是，根据我找到的链接，你可以尝试一下。

#4

Are they the exact same data type? If not, for Access, try using LIKE instead of equals.

它们是完全相同的数据类型吗?如果不是，为了访问，尝试使用LIKE而不是equals。

t1.parameter_name like t2.parameter_name

t1。parameter_name像t2.parameter_name

See the example here. http://office.microsoft.com/en-us/access-help/access-sql-where-clause-HA010278156.aspx

在这里看到的例子。http://office.microsoft.com/en-us/access-help/access-sql-where-clause-HA010278156.aspx

#5

If your table is linked and the underlying datatype is char rather than varchar, then it will be blank padding even after you think you have trimmed it. You'll have to change the underlying data type or join on trim(parameter_name) which will be expensive!

如果您的表是链接的，底层数据类型是char而不是varchar，那么即使您认为已经对其进行了修改，它也将是空白填充。您将不得不更改底层数据类型或trim上的join (parameter_name)，这将非常昂贵!

#1

SELECT
    Parameter_Name,
    Len(Parameter_Name) AS Length_of_Parameter_Name
    Asc(Right(Parameter_Name, 1)) AS rightmost_character
FROM ParameterTable;