Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA/SQL Queries (2002)

    Hi all, am having great fun trying out VBA - can you tell me when you are cutting and pasting a query into VBA window to use CurrentDB.Execute - and its a long query - how do you get it to behave at the end of a line - it puts in the " before I need it and I don't know how to get it to ignore that break. Works fine for smaller queries. Can you help?

    Ina

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

    Re: VBA/SQL Queries (2002)

    You could do it like this:

    - Copy the SQL string from the SQL view of a query (Ctrl+C).
    - Paste it into a blank Word document (Ctrl+V).
    - Select Edit | Replace.
    - Enter <code>^p</code> in the 'Find what' box.
    - Enter a space in the 'Replace with' box.
    - Click Replace All.
    - Enter a double quote [code["[/code] in the 'Find what' box.
    - Enter a single quote (apostrophe) <code>'</code> in the 'Replace with' box.
    - Click Replace All.
    - Close the Replace dialog.
    - Select the string (but don't select the final paragraph mark at the end).
    - Copy it to the clipboard.
    - Type a pair of double quotes <code>""</code> in the Visual Basic Editor.
    - Paste the SQL string between the quotes.

    You may want to break up a very long string for readability:
    <code>
    strSQL = "SELECT Field1, Field2 FROM tblSomething " & _
    "WHERE Field1 > 3 AND Field2 ='Jones' " & _
    "ORDER BY Field2"
    </code>
    I usually end up adjusting the line breaks manually...

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA/SQL Queries (2002)

    yikes. Will try the latter option. Thanks!

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA/SQL Queries (2002)

    That worked fine, except where I have a static value for fields I am inputting (everything has to be defined as text, even numbers) - its kicking me out everywhere there are ""s e.g. "3"

    CurrentDb.Execute "SELECT FAR.Division, "3" AS Company, FAR.FinFleet AS Vehicle, "1" AS SubNumber, IIf([Depr To Date]<[Cost],"1","8") AS Status, ...etc.

    Obviously its something to do with the quotation marks - how do I deal with this? Thanks muchly.

    Ina

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

    Re: VBA/SQL Queries (2002)

    Either replace the double quotes in the SQL string with single quotes (apostrophes) or with double double quotes.

Posting Permissions

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