表达式中键入不匹配。在访问数据库中 [英] Type mismatch in expression. in access database

问题描述

cmdselect.CommandText = "SELECT PD.Prod_ID, PD.Prod_BrandName, PD.Prod_ModelNo, SUM(iif(Isnull(PM.Prod_Qty), 0, PM.Prod_Qty)) AS Purchase_Item, SUM(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) AS Sale_Item, SUM(iif(Isnull(PM.Prod_Qty), 0, PM.Prod_Qty)) - SUM(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) AS Balance_Item FROM ((tbl_ProductDetails PD LEFT OUTER JOIN tbl_PurchaseMaster PM ON PD.Prod_ID = PM.Prod_ID) LEFT OUTER JOIN tbl_SaleMaster SM ON PD.Prod_ID = SM.Prod_ID) GROUP BY PD.Prod_ID, PD.Prod_ModelNo, PD.Prod_BrandName";

im使用此查询从访问数据库中检索数据但我收到错误消息

i m using this query to retrive data from access database but i m getting a error message

"Type mismatch in expression".

and i know that how it's give error because my column PD.Prod_ID is AutoNumber datatype.

and 
PM.Prod_ID is Number datatype

SM.Prod_ID is also Number Datatype.

但我不知道我是如何解决这个问题的......



所以任何人都帮助我....

but i dont know how i solve this problem...

so any one help me....

推荐答案

我可以给你一个技巧来解决你的问题，虽然我不能告诉你到底出了什么问题。 。



1.用SUM（...）取出所有行并运行这样的查询。

这不应该不给你任何问题 - 这只是给你你的基线。



2.将你取出的部分一次放回一个......例如put SUM（iif（Isnull（SM.Prod_Qty），0，SM.Prod_Qty））AS Sale_Item



我希望你得到错误，但如果没有把buy_item的计算重新放入并再次运行查询。



换句话说 - 技术是从简单的东西开始，确保它有效，然后添加额外的位 - 每次它仍然有效时进行测试。当它失败时你会知道它是你改变的最后一件事。



其他几点......你还没有说你正在使用什么数据库，但在SQL中，函数Isnull采用两个参数 - 您要查询的列，然后是一个值，如果它返回null则替换。所以尝试用替换 iif（Isnull（SM.Prod_Qty），0，SM.Prod_Qty） 如果你的数据库迎合了它 - 它更整洁，更容易阅读。



最后 - 我认为没有必要对于FROM子句的括号 - 它们只是添加noise

I can give you a technique to solve your problem although I can''t tell you exactly what is wrong ...

1. Take out all of the lines with SUM(...) in and run the query like that.
This shouldn''t give you any problems - this just gives you your baseline.

2. Put the sections you took out back in one at a time ... e.g. put SUM(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) AS Sale_Item

I''m expecting you to get the error, but if not put the calculation for purchase_item back in and run the query again.

In other words - the technique is to start with something simple, make sure it works, then add in extra bits - testing each time that it still works. When it fails you know it''s the last thing you changed.

A couple of other points ...You haven''t said what database you are using, but in SQL the function Isnull takes two parameters - the column you are querying and then a value to substitute if it returns a null. So try replacing iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty) with Isnull(SM.Prod_Qty,0) if your database caters for that - it''s a lot neater and easier to read.

And finally - I don''t think there is any need for the brackets around your FROM clause - they just add "noise"

这篇关于表达式中键入不匹配。在访问数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！