Type mismatch in expression. in access database

Question

0.00/5 (No votes)

See more:

C#

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

F#

"Type mismatch in expression".

F#

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....

Posted 18-Jan-13 20:58pm

Shambhoo kumar

Updated 18-Jan-13 21:03pm

v2

Add a Solution

Comments

Shambhoo kumar 19-Jan-13 4:10am

can any one help me..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2013-01-19T06:35:00

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"