I have a combo box called NameFilt. I wish the row source to be as follows:
SELECT DISTINCT VoucherListTbl.BuyerName
FROM VoucherListTbl
WHERE (((VoucherListTbl.BuyerName) Is Not Null)) OR (((VoucherListTbl.BuyerName)<>""));
i.e show all the unique BuyerNames from my table and dont include any blanks The above SQL is generated in the query bulider by clicking on the 3 dots in combo box's row source in the property sheet, then selecting the BuyerName field and then entering Is Not Null Or <>" in the criteria. Clicking run in the query builder displays the exact result I expect.
On closing and saving the query builder and then clicking in the combo box on the form I get a different result - All the DISTINCT names are there, but there is a blank at the start of the list. When I attempt to use this SQL in my VBA code I get another result. The code is:
Private Sub NameFilt_GotFocus()
Me.AllowEdits = True
Me.NameFilt.RowSource = "SELECT DISTINCT VoucherListTbl.BuyerName
FROM VoucherListTbl WHERE (((VoucherListTbl.BuyerName) Is Not Null))
OR (((VoucherListTbl.BuyerName)<>""));"
Me.NameFilt.Dropdown
End Sub
This results in the combo box's dropdown showing only one option - a blank! There are no names listed. Moreover, If the WHERE clause is removed i.e. the code is:
Private Sub NameFilt_GotFocus()
Me.AllowEdits = True
Me.NameFilt.RowSource = "SELECT DISTINCT VoucherListTbl.BuyerName FROM VoucherListTbl;"
Me.NameFilt.Dropdown
End Sub
Then the DISTINCT names are shown, with a blank option at the top of the list which is what one would expect
Please could you help by explaining why the WHERE clause will not work for me when entered into the VBA code Many thanks
If you use "" within "" it will break the string and that's the reason of non-working WHERE condition. Encode quotes with "" i.e. end of your original string should look like <>""""));" or replace "" with '' and try again.
Note, same query can be written as
SELECT DISTINCT BuyerName
FROM VoucherListTbl
WHERE IsNull(BuyerName,'')<>''