Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria ignored (Access2003)

    Good Day!

    I have following to execute

    strSQL = "Select * from [Starting HC] where [Starting HC].LOB = 'AB' or "CD' or 'EF' " & _
    " ORDER BY [Starting ABC].LAST_NAME, [Starting ABC].FIRST_NAME"
    Set qtmp = dbs.CreateQueryDef(strTemp, strSQL)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, strReportName
    DoCmd.DeleteObject acQuery, strTemp

    It works well without taking 'AB' or "CD' or 'EF' into consideration. It brings whole alphabet. Please, help. I suspect single quotes are messing up but I had tied single in double, ouside double etc...THANKS

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Criteria ignored (Access2003)

    Your WHERE statement is faulty. It needs to be:

    " WHERE [Starting HC].LOB IN ('AB', 'CD', 'EF' ) "

    or

    " WHERE [Starting HC].LOB = 'AB' or [Starting HC].LOB = 'CD' or [Starting HC].LOB = 'EF' "
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Criteria ignored (Access2003)

    Try
    <code>
    strSQL = "Select * from [Starting HC] where [Starting HC].LOB = 'AB' or [Starting HC].LOB = 'CD' or [Starting HC].LOB = 'EF' " & _
    </code>
    or, somewhat more efficient
    <code>
    strSQL = "Select * from [Starting HC] where [Starting HC].LOB In('AB', 'CD', 'EF') " & _
    </code>
    Note: you're selecting records from Starting HC but ordering by fields from Starting ABC. Is that correct?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria ignored (Access2003)

    Thanks Mark, Hans! I could never understood IN thing...:-)

    And Hans, I had just missed changing HC to ABC for better picture so it is all right! THANKS

Posting Permissions

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