条件表达式中的数据类型不匹配 - 查询 [英] Datatype mismatch in criteria expression - query
问题描述
你好 -
我是MS Access的新手而且不懂SQL。我正在使用Access 2003.
我已经设置了两个似乎工作正常的查询。每个都直接来自一张桌子。我已经在包括A中的所有记录中加入了两个查询，并且只有来自B的那些连接字段相等的记录。加入。
我用于连接的字段在各自的查询中都是固定的十进制，零小数位字段。
I我在标准表达式中得到数据类型不匹配错误，但不知道如何去解决问题。
我从阅读其他帖子中收集到问题甚至可能不在加入字段中，但是可能在其他地方。但是我该如何跟踪它？
来自联接的SQL代码如下。
非常感谢，
Kristin Graves
Hello -
I am a novice in MS Access and do not know SQL. I am using Access 2003.
I have set up two queries that seem to be working fine. Each comes directly from a table. I have joined the two queries in a "include all records from A and only those records from B where the joined fields are equal" join.
The fields that I am using for the join are both fixed decimal, zero decimal place fields in their respective queries.
I am getting the "datatype mismatch in criteria expression" error, but have no idea how to go about de-bugging to track down the problem.
I gather from reading other posts that the problem may not even be in the join fields, but could be elsewhere. But how do I go about tracking this down?
The SQL code from the join is below.
thanks very much in advance,
Kristin Graves
推荐答案
SQL中没有WHERE子句声明，所以我猜它与加入有关。
您从表加入Field [Old Mtr＃] 来自表 mv90_info 的字段米的gibbons_starting_meter_list
开始调试是的，我建议在设计视图中打开每个表，并在每个表中查找这些字段，并检查数据类型。它可能是字符串或数字。如果它的数量，你还需要检查它是什么类型的数字，字节，整数，长，单，双。
报告回到这里，我们可以建议你如何继续。
如果你从每个表中的每个连接字段发布一些条目，这也会有所帮助，所以我们可以告诉你它是否安全。更改数据类型，以便允许连接。
You have no WHERE clause in your SQL statement, so I would guess that it IS related to the join.
Your joining Field [Old Mtr #] from Table gibbons_starting_meter_list with Field Meter from table mv90_info
To start debugging this, I would suggest opening each of the tables in design view, and finding those Fields in each table, and checking the datatype. Its likely to be either String or number. If its Number, you also need to check what KIND of number it is, Byte, Integer, Long, Single, Double.
Report that back here, and we can advice you on how to proceed.
It would also help if you post a few entries from each of the joined fields, from each table, so we can tell you if its "safe" to change the datatype, in order to allow the join.
也可以想到错误实际上来自一个或两个子查询。
和Smiley一样，我会要求你在尝试单独运行每个子查询后报告回来。
玩得开心，欢迎光临字节！
It''s also conceivable that the error is actually coming from one or both of the subqueries.
Like Smiley, I''ll request that you report back after you''ve tried running each of the subqueries individually.
Have fun and Welcome to Bytes!
好的，这里有关于我的设置和我的字段格式的更多信息。
我开始有两个表，将每个表读入查询，然后当我尝试将两个查询连接到第三个查询时出现问题。
这是关于该字段的信息我的两个连接字段的格式：
[Old Mtr＃]字段
[Old Mtr＃]字段的起点表A中，字段名为E_MTR_NO，格式为数字，长整数，固定，0位小数。
读取表A的查询称为g ibbons_starting_meter_list。在该查询中，我按如下方式重命名E_MTR_NO：Old Mtr＃：E_MTR_NO - 格式固定，0位小数。每个记录都填充一个非零值。
一些样本记录如下：
Old Mtr＃
7116190
7117022
7719119
7144228
7144239
19893
7424026
[Meter]字段
[Meter]字段的起点是表B.在表B中，该字段称为RecorderID，它是一个文本字段，其中嵌入了仪表编号 - 我在查询中修复了该字段。具体格式是文本（我没有看到任何其他参数分配）。
读取表B的查询称为mv90_info。在该查询中，我按如下方式重新命名并修复RecorderID：Meter：IIf（Left（[RecorderID]，3）=" REC"，Val（Mid（[RecorderID]，4,7）），Val（[RecorderID] ]）） - 格式固定，小数点后0位。有相当多的记录值为0.
样本记录如下：
米
90370107
40360309
6596820
6815256
6815261
87940
87941
87942
再次感谢大家提供的任何帮助。
OK, here is a little more about my set-up and the format of my fields.
I start with two tables, read each of those tables into a query, and then the problem comes when I try to join the two queries into a third query.
Here is the information about the field formats for my two join fields:
[Old Mtr #] field
The starting point for the [Old Mtr #] field is table A. In table A, the field is called E_MTR_NO, and the format is number, long integer, fixed, 0 decimal places.
The query that reads table A is called gibbons_starting_meter_list. In that query, I re-name E_MTR_NO as follows: Old Mtr #: E_MTR_NO -- the format is fixed, 0 decimal places. Each record is populated with a non-zero value.
Some sample records follow:
Old Mtr #
7116190
7117022
7719119
7144228
7144239
19893
7424026
[Meter] field
The starting point for the [Meter] field is table B. In table B, the field is called RecorderID, and it is a text field with the meter number embedded in it - I fix that in the query. The specific format is text (I don''t see any other parameters assigned).
The query that reads table B is called mv90_info. In that query, I re-name and fix RecorderID as follows: Meter: IIf(Left([RecorderID],3)="REC",Val(Mid([RecorderID],4,7)),Val([RecorderID])) -- the format is fixed, 0 decimal places. There are quite a number of records that take a value of 0.
Sample records follow:
Meter
90370107
40360309
6596820
6815256
6815261
87940
87941
87942
Thanks again for any help you folks can provide.
这篇关于条件表达式中的数据类型不匹配 - 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
