Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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";

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.

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....
Updated 18-Jan-13 21:03pm
Shambhoo kumar 19-Jan-13 4:10am    
can any one help me..

1 solution

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"
Share this answer
Shambhoo kumar 19-Jan-13 12:45pm    
thanks for recomend......
CHill60 19-Jan-13 15:20pm    
My apologies - I've just realised that you did actually say in the title that it was an Access database you are using - so you are right to use Isnull with IIF (or you might be able to use the nz function depending on how you connect).
Hope my suggested technique helps you find the problem

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900