Results 1 to 12 of 12

Thread: Please Help

  1. #1
    Join Date
    Sep 2004
    Posts
    37

    Unanswered: Please Help

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    37
    Thanks for the effort but It is still yeilding the same error. any other ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    double-check the datatypes of all columns involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    37
    How will I identify mistakes after looking at the data types of each column involved in the query? Thanks again for your continued support!


  6. #6
    Join Date
    Oct 2003
    Posts
    1
    (((TradeHist.[Portfolio Code]='2AK') Or (TradeHist.[Portfolio Code]='42O') Or (TradeHist.[Portfolio Code])='4UZ'));

    Try this

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Sep 2004
    Posts
    37
    Thanks all. Ill be at work in a bit. Ill let you know what I find!!!

  9. #9
    Join Date
    Sep 2004
    Posts
    37
    It worked!! Thanks

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mind telling us what worked?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    heh

    i always say something like "my crystal ball runs on Windows™ ME© and is down at the moment"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •