[gissa]

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]

put your conditions into the WHERE clause

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]

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

[r937]

double-check the datatypes of all columns involved

[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!

[roshanpm]

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

Try this

[Pat Phelan]

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

[gissa]

Thanks all. Ill be at work in a bit. Ill let you know what I find!!!

[gissa]

It worked!! Thanks

[r937]

mind telling us what worked?

[Pat Phelan]

yeah, I'm curious too. I can't seem to engage omniscient mode at the moment, so I'd like a wee bit more feedback.

-PatP

[r937]

heh

i always say something like "my crystal ball runs on Windows™ ME© and is down at the moment"