Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    10

    Unanswered: You folks are too kind.

    I really don't know who else to turn to when I get stumped. Thank you!

    This Access SQL Query written in VB works fine until I add the sortkeys. Then I get a 'missing parameter, expected 1' error. I can't see the problem.

    SQL = "SELECT 1 as sortkey1, Driver2.BusNumber as sortkey2, Driver.BusNumber," &_
    " " & "First, Last, HomePhone, CellPhone, Parking" &_
    " " & "FROM Driver2, Bus WHERE Driver2.BusNumber = Bus.BusNumber" &_
    " " & "AND Last <>'' AND Last <> 'Spare Bus' AND Last <> 'Activity'" &_
    " " & "UNION ALL" &_
    " " & "SELECT 2 as sortkey1, BusNumber as sortkey2, BusNumber, First, Last, HomePhone, CellPhone, Blank" &_
    " " & "FROM PdSub WHERE Last <>''" &_
    " " & "UNION ALL" &_
    " " & "SELECT 3 as sortkey1, BusNumber as sortkey2, BusNumber, First, Last, HomePhone, CellPhone, Blank" &_
    " " & "FROM Sub WHERE Last <>''" &_
    " " & "UNION ALL" &_
    " " & "SELECT 4 as sortkey1, Attnd.BusNumber as sortkey2, Attnd.BusNumber, First, Last, HomePhone, CellPhone, Parking" &_
    " " & "FROM Attnd, Bus WHERE Attnd.BusNumber = Bus.BusNumber AND Last <>''" &_
    " " & "UNION ALL" &_
    " " & "SELECT 5 as sortkey1, BusNumber as sortkey2, BusNumber, First, Last, HomePhone, CellPhone, Blank" &_
    " " & "FROM AttndPdSub WHERE Last <>''" &_
    " " & "UNION ALL" &_
    " " & "SELECT 6 as sortkey1, BusNumber as sortkey2, BusNumber, First, Last, HomePhone, CellPhone, Blank" &_
    " " & "FROM AttndSub WHERE Last <>'' ORDER BY sortkey1, sortkey2"

    Can you folks see what I can't. I've been working with it too long.

    Thanks again.

    Lee

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    The best way of debugging these sort of things is to have a look at the sql once its complete and paste it into a blank query. As this is a union you will need to do it one section at a time running each select query reperately until you find the problem.

    Personally I prefer to save queries (rather than doing a lot on .runsql strBigQuery). I feel this makes it easier on the next developer (as well as the current one!)

Posting Permissions

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