Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    21

    Unanswered: ORDER BY Clause in SQL Statement

    Hi Guys,

    I'm getting stuck with the syntax on this one:

    strSQL = "SELECT tblPaidAmounts.PaidID, tblPaidAmounts.PaidDate, tblPaidAmounts.Branch, " & _
    "tblPaidAmounts.Collect, tblPaidAmounts.Discount, [collect]-[discount] AS AfterDiscount, " & _
    "tblPaidAmounts.ExpAmt, [AfterDiscount]-[expamt] AS AfterExpenses, tblPaidAmounts.DepositAmount, [afterexpenses]-[depositamount] AS Balance " & _
    "FROM tblPaidAmounts INNER JOIN tblbranches AS Branch ON branch.branchid = tblpaidamounts.branch " & _
    "WHERE tblpaidamounts.Branch = " & Branchid
    "ORDER BY paiddate DESC"

    So Branchid is a global variable (I think) or it comes from a table when the user logs into the database, trouble is I can't encapsulate the branchid with a another & or use " & _ as I keep on getting unterminated string errors???

  2. #2
    Join Date
    Feb 2010
    Posts
    21
    OK before I get flamed, let me put in a brief explanation, I tried to delete this post but I couldn't.

    I managed to sort it out, I should have put a space in between the quote marks.

    So this one is correct:

    strSQL = "SELECT tblPaidAmounts.PaidID, tblPaidAmounts.PaidDate, tblPaidAmounts.Branch, " & _
    "tblPaidAmounts.Collect, tblPaidAmounts.Discount, [collect]-[discount] AS AfterDiscount, " & _
    "tblPaidAmounts.ExpAmt, [AfterDiscount]-[expamt] AS AfterExpenses, tblPaidAmounts.DepositAmount, [afterexpenses]-[depositamount] AS Balance " & _
    "FROM tblPaidAmounts INNER JOIN tblbranches AS Branch ON branch.branchid = tblpaidamounts.branch " & _
    "WHERE tblpaidamounts.Branch = " & Branchid & " " & _
    "ORDER BY paiddate DESC"

    And then it works.

    OK So flame away, sorry guys

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Response.Write()...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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