Please Help

gissa · #1 (**permalink**) 09-30-04, 12:37

I am creating a query and am getting:
"Data Type Mismatch in criteria expression" error messages!!

It's driving me crazy. It only happens when I enter certain codes. For example, if I enter "2AP" the query works. If i enter "2AK", I receive the error!

Here is the SQL although I used Design View in Access to create the query:

SELECT TradeHist.[Portfolio Code], Sum([Trade Shares]*[Trade FX Rate]*[Order Decision Price]) AS MV, Sum([Trade Shares]*[Trade FX Rate]*[Order Decision Price]*[OrderHor])/[MV] AS OHMV, Sum(IIf([OrderHor]>=0.5,1,0)*[Trade Shares]*[Trade FX Rate]*[Order Decision Price])/[MV] AS PercOverFifty
FROM OrderHorizonQuery INNER JOIN TradeHist ON OrderHorizonQuery.[Order ID Master] = TradeHist.[Order ID Master]
GROUP BY TradeHist.[Portfolio Code]
HAVING (((TradeHist.[Portfolio Code])="2AK" Or (TradeHist.[Portfolio Code])="42O" Or (TradeHist.[Portfolio Code])="4UZ"));

Any help would be appreciated!

r937 · #2 (**permalink**) 09-30-04, 15:01

put your conditions into the WHERE clause

Code:

select TradeHist.[Portfolio Code]
     , Sum([Trade Shares]*[Trade FX Rate]*[Order Decision Price]) AS MV
     , Sum([Trade Shares]*[Trade FX Rate]*[Order Decision Price]*[OrderHor])/[MV] AS OHMV
     , Sum(IIf([OrderHor]>=0.5 ,1 ,0)*[Trade Shares]*[Trade FX Rate]*[Order Decision Price])/[MV] AS PercOverFifty
  from OrderHorizonQuery 
inner 
  join TradeHist 
    on OrderHorizonQuery.[Order ID Master] 
     = TradeHist.[Order ID Master]
 where TradeHist.[Portfolio Code] in ('2AK','42O','4UZ')
group 
    by TradeHist.[Portfolio Code]

gissa · #3 (**permalink**) 09-30-04, 16:42

Thanks for the effort but It is still yeilding the same error. any other ideas?

r937 · #4 (**permalink**) 09-30-04, 16:51

double-check the datatypes of all columns involved

gissa · #5 (**permalink**) 09-30-04, 22:53

How will I identify mistakes after looking at the data types of each column involved in the query? Thanks again for your continued support!

roshanpm · #6 (**permalink**) 09-30-04, 23:30

(((TradeHist.[Portfolio Code]='2AK') Or (TradeHist.[Portfolio Code]='42O') Or (TradeHist.[Portfolio Code])='4UZ'));

Try this

Pat Phelan · #7 (**permalink**) 10-01-04, 00:06

Quote:

Originally Posted by gissa

How will I identify mistakes after looking at the data types of each column involved in the query? Thanks again for your continued support!

Based on your posted code, [OrderHor] should be a numeric type, and [Portfolio Code] should be a text type. I'd guess that one of them is of the wrong type.

-PatP