Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Query (2000/3)

    Hi all,

    I have a small problem with a query, the are 2 WHERE statements but I can only get it to work if I comment out the last one. I think I have the code wrong for two or more WHERE statements. I can get it working with one no problem.

    Here's the code

    strDb = ("charliedocumentsLetterLog.mdb")
    strSQL = "SELECT CRN, Number, Surname, ForeName, DOB, DDate, MDSQ1, MDSQ2, MDSQ3, MDSQ4, MDSQ5, UPI FROM QryF2Details " & _
    "WHERE CRN = " & Chr(34) & strCRN & Chr(34) AND "WHERE DDate =" Chr(34) & strCDate & Chr(34)
    Debug.Print strSQL
    Selection.Range.InsertDatabase DataSource:=strDb, _
    SQLStatement:=strSQL, IncludeFields:=True

    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: SQL Query (2000/3)

    You can have only one WHERE in an SQL string (but it can be followed by multiple conditions with AND or OR.
    You have placed the word AND outside the quotes so it will not be part of the SQL string.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Query (2000/3)

    Thank you Hans.

    When you say outside the quote do you mean

    strSQL = "SELECT ADDRESS FROM QryF2Details " & "WHERE CRN = " & Chr(34) & strCrn & Chr(34) AND "REF = " & Chr(34) & strREF & Chr(34)

    Where the AND is not wrapped in anything.

    Many thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: SQL Query (2000/3)

    You want the word AND to be part of the SQL string so it should be inside quotes, not outside:
    <code>
    strSQL = "SELECT ADDRESS FROM QryF2Details " & _
    "WHERE CRN = " & Chr(34) & strCrn & Chr(34) & " AND REF = " & Chr(34) & strREF & Chr(34)
    </code>
    This assumes that both CRN and REF are text fields.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Query (2000/3)

    Oh I see

    Thanks Hans
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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