Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox Rowsource problem (Access 2000)

    I'm trying to populate a listbox lbxSelect with the result of a query which is trying to select all records in the form's dataset (qryInvNonMembers) where the first letter of 'OwnerRecordName' is strLetter (which is derived from a set of buttons).

    Forms!frmInvoiceReceipts!lbxSelect.RowSource =
    "SELECT qryInvNonMembsSelect.OwnerID, qryInvNonMembsSelect.OwnerRecordName
    FROM qryInvNonMembsSelect
    WHERE (((qryInvNonMembsSelect.OwnerRecordName) Like " & Chr(34) & strLetter & "*" & Chr(34)))
    ORDER BY qryInvNonMembsSelect.OwnerRecordName;"

    It baulks on any and all combinations of the brackets in the where clause and I'm lost as to what is wrong.

    David

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

    Re: Listbox Rowsource problem (Access 2000)

    If you break one instruction into several lines, you must use continuation characters _ after each line except the last one, and you must concatenate the pieces of the string (each surrounded by quotes) with &.

    Forms!frmInvoiceReceipts!lbxSelect.RowSource = _
    "SELECT OwnerID, OwnerRecordName" & _
    " FROM qryInvNonMembsSelect" & _
    " WHERE OwnerRecordName Like " & Chr(34) & strLetter & "*" & Chr(34) & _
    " ORDER BY OwnerRecordName;"

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Listbox Rowsource problem (Access 2000)

    Try this:

    <code>Forms!frmInvoiceReceipts!lbxSelect.RowSour ce =
    "SELECT qryInvNonMembsSelect.OwnerID, qryInvNonMembsSelect.OwnerRecordName
    FROM qryInvNonMembsSelect
    WHERE qryInvNonMembsSelect.OwnerRecordName Like " & Chr(34) & strLetter & "*" & Chr(34)
    ORDER BY qryInvNonMembsSelect.OwnerRecordName;"</code>
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Rowsource problem (Access 2000)

    Thanks Charlotte & Hans, working fine now.

    Hans, I habitually write this sort of code as one long line until I know it works correctly. Only then do I break the line up as you show. I broke the lines in my post to cut its width for readability.

Posting Permissions

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