查询中条件表达式中的数据类型不匹配 [英] Data type mismatch in criteria expression in query

问题描述

我编写了一个查询，它带有三个表示日，月和年的整数，从中形成一个日期，并将此日期与输入记录的日期进行比较，并返回日期超过10个月的所有记录。 br />

查询运行正常，但是当我将标准> 10时，我得到数据类型不匹配错误。下面的代码是原始查询。我已经把所有的datediff位放在代码中，所有变量都声明为日期，字符串，整数等，并在查询中调用该函数，再次，数据类型不匹配


我终于在代码中做了比较，并且有一个返回true或false的布尔函数。查询运行了，但是当我输入标准返回TRUE时，我再次得到''数据类型不匹配''


这是一个关键的数据验证，我要去在这里疯狂试图弄清楚我做错了什么

展开 | 选择 | Wrap < span class =codeDivider> | 行号

解决方案

< blockquote>嗨

我写了一个查询，它带有代表日，月和年的三个整数，形成一个日期，并将这个日期与记录的日期进行比较进入并重新开始转动任何日期超过10个月的记录。


查询运行正常，但是当我将标准> 10时，我得到数据类型不匹配错误。下面的代码是原始查询。我已经把所有的datediff位放在代码中，所有变量都声明为日期，字符串，整数等，并在查询中调用该函数，再次，数据类型不匹配


我终于在代码中做了比较，并且有一个返回true或false的布尔函数。查询运行了，但是当我输入标准返回TRUE时，我再次得到''数据类型不匹配''


这是一个关键的数据验证，我要去在这里疯狂试图弄清楚我做错了什么

展开 | 选择 | Wrap < span class =codeDivider> | 行号

嗨


这可能是一个很长的镜头，但试试这个


WHERE（（（tblWomen.CurrentlyPregnant） =真）和（（tblWomen.IfyesLMPDay）不是空的）和（（tblWomen.IfyesLMPMonth）不是Nul l）AND（（tblWomen.IfyesLMPYear）不为空）和（（DateDiff（" m"），DateValue（[ifyeslmpday]& " - " &安培; [ifyeslmpmonth]& " - " &安培; [ifyeslmpyear]），[whenentered]）> 10）））;


我已经移动了>左边有一个支架????


MTB

感谢Mike，但SQL是由Access Query Editor创建的，未输入。在查询编辑器中没有移动的括号。有趣的是，我进入了SQL视图并确实移动了支架，在设计模式下，没有任何改变，我本以为设计视图会失败。仍然有数据类型不匹配。

感谢Mike，但SQL是由Access Query Editor创建的，未输入。在查询编辑器没有要移动的括号。有趣的是，我进入了SQL视图并确实移动了支架，在设计模式下，没有任何改变，我本以为设计视图会失败。仍然有数据类型不匹配。

仍然无法解决这个问题，而且它变得至关重要。我已经简化了表达式并且只归结为日期值函数，如下所示


Expr1：DateValue（[ifyeslmpday]&" - "& [ifyeslmpmonth]&" ; - "& [ifyeslmpyear]）


所有字段均有效且正确。 Expr1返回预期的日期，但是一旦我对它提出任何标准，例如，只返回＃02/03/1958＃我得到数据不匹配错误。它似乎没有返回日期（我也尝试了数字和文本，没有什么可以让我选择针对Expr1的标准。


任何想法任何人。

I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the date is more than 10 months out.

The query runs fine, but I when I put the criteria of >10 I get ''Data Type mismatch'' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date, string, integer etc and called the function in the query, again, "Data type mismatch"

I finally did the comparison in code and had a boolean function returning true or false. The query ran, but when I put in the criteria to return TRUE, I again got the ''Data Type Mismatch''

This is a critical bit of data validation and I am going mad here trying to work out what I am doing wrong

Expand|Select|Wrap|Line Numbers

解决方案

Hi

I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the date is more than 10 months out.

The query runs fine, but I when I put the criteria of >10 I get ''Data Type mismatch'' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date, string, integer etc and called the function in the query, again, "Data type mismatch"

I finally did the comparison in code and had a boolean function returning true or false. The query ran, but when I put in the criteria to return TRUE, I again got the ''Data Type Mismatch''

This is a critical bit of data validation and I am going mad here trying to work out what I am doing wrong

Expand|Select|Wrap|Line Numbers

Hi

This may be a long shot but try this

WHERE (((tblWomen.CurrentlyPregnant)=True) AND ((tblWomen.IfyesLMPDay) Is Not Null) AND ((tblWomen.IfyesLMPMonth) Is Not Null) AND ((tblWomen.IfyesLMPYear) Is Not Null) AND ((DateDiff("m",DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear]),[whenentered])>10)));

I have move >10 one bracket to the left ????


MTB

Thanks Mike but the SQL was created by the Access Query Editor, not typed in. In the query editor there are no brackets to move against. Interestingly, I went to the SQL view and did move the bracket, and in design mode, nothing had changed, i would have expected the design view to fail. Still got the Data Type Mismatch though.

Thanks Mike but the SQL was created by the Access Query Editor, not typed in. In the query editor there are no brackets to move against. Interestingly, I went to the SQL view and did move the bracket, and in design mode, nothing had changed, i would have expected the design view to fail. Still got the Data Type Mismatch though.

Still not able to solve this problem, and its becoming critical. I have simplified the expression and have come down to just a datevalue function as below

Expr1: DateValue([ifyeslmpday] & "-" & [ifyeslmpmonth] & "-" & [ifyeslmpyear])

All the fields are valid and correct. Expr1 returns the expected date, but as soon as I put any criteria against it, like, only return #02/03/1958# I get the Data Mismatch error. It does not seem to be returning a date (I have also tried numbers and text, nothing will let me select a criteria against Expr1.

Any ideas anyone.

这篇关于查询中条件表达式中的数据类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！