Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Where clause (2000 SP3)

    Can someone tell me what is wrong with this picture, please?
    "SELECT LOC, Funding From qryTest WHERE LOC ='" & strLOC "' And LOC Not Like 'US *';"

    The query works in Access, but when I try to run it from code, I can't get it to exclude the 'US *' (the field actually contains a country name, and in the case of the US it is in the form 'US - XX' where XX is the 2-letter state code.

    I am sure it is something simple, but I can't think of what it might be!

    Thanks,

    Kiwi44

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

    Re: SQL Where clause (2000 SP3)

    There is an ampersand missing after strLOC:

    "SELECT LOC, Funding From qryTest WHERE LOC ='" & strLOC & "' And LOC Not Like 'US *';"

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Where clause (2000 SP3)

    Thanks - but that doesn't work either. The statement still selects all records in qryTest. I have also tried taking it one step back to the SQL statement that leads to population of strLOC, and the "Not LIke 'US *'" doesn't work either.

    Kiwi44

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

    Re: SQL Where clause (2000 SP3)

    I don't think there is a way we can help you further without seeing the database. If you like, you can post a stripped down version of the database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Where clause (2000 SP3)

    Thanks for the suggestion. In another post I discovered that simply cutting and pasting macros into VB code does not work (guilty as charged!), so I need to spend a little time cleaning up the code I have. I will come back if there are still problems after I have done that.

    kiwi44

Posting Permissions

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