Listbox Rowsource problem (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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;”

Reply | Quote

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.

Reply | Quote

Try this:

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

Reply | Quote