Sort List Box from Combo - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Sort List Box from Combo

Brent Rinehart

5 years ago

I have two combo boxes that need to filter a list box (BidList), Filter by Office and Year the year is being filtered from the BidDate from the PropT, which stores the full date. YearCombo is populated from YearT and is unbound, Column 0 is YearID, Column 1 is YearSort and Column 2 is YearNumber. OfficeCombo is populated from OfficeT and is bound to OfficeName, Column 0 is OfficeID and Column 1 is OfficeName.

"SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)= YearCombo.column(3) " & _
    "AND OfficeName = OfficeNameCombo.column(2) " & _
    "ORDER BY " & Forms!PropListF!SortBy

My issue I think is always syntax no matter how much I do this grasping this syntax hopfully will kick in someday.

	Kevin Robertson @Reply 5 years ago
Hi Brent, You are referencing your ComboBoxes inside the quotes. Try this: "WHERE YEAR(BidDate)=" & YearCombo.column(3) & " " & _ "AND OfficeName =" & OfficeNameCombo.column(2) & " " & _

	Richard Rost @Reply 5 years ago
Don't forget quotes around OfficeName `"AND OfficeName = """ & OfficeNameCombo.column(2) & """ " & _`

Brent Rinehart OP @Reply

5 years ago

Thank You guys for helping me out I really appreciate it. as soon as I got home I attempted what you guys said and its still giving me a compile error. These quotes are the death of me. Bellow is what I tried.
Looks like I missed something I updated doesnt give me compile error now, but when I select either box it doesnt sort I get a blank list box I will need to dig further maybe something else now is the problem.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
    "AND OfficeName= """ & OfficeNameCombo.Column(2)& """ "  & _
    "ORDER BY " & Forms!PropListF!SortBy

	Adam Schwanz @Reply 5 years ago
Ambersands are inside of quotes after the both the words column

	Adam Schwanz @Reply 5 years ago
Actually maybe I'm lost in all the line breaks too, just make a textbox on the form called status then make status = bidlist.rowsource in your code, so you can see what it's actually saying.

Scott Axton @Reply

5 years ago

Brent -
I often find that putting it back into the query builder and letting Access help saves me a lot of headaches sometimes.

Another thing I do is make it work first by putting in known data. Then 1 by 1 switch out the known data with the variables.

Something that helps me is to think of the variables as including the &.  So...  & variable &

"WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
      above is missing the & before YearCombo.
so & YearCombo.column(3) &

Something else that is helpful for me is to set the sql string to a variable and using Richards StatusBox or even a MsgBox look at what the sql is converting to.  Like this:



     Dim SQL As String



     SQL = "SELECT BidID, JobName, JobCity, EnteredDate ..."    ' Enter the rest of the SQL string here.

    

     MsgBox SQL

   

   ' Once You have it working set the RowSource and remark out the MsgBox



    ' BidList.RowSource = SQL

Scott Axton @Reply

5 years ago

You also need ending quotes after  "ORDER BY " & Forms!PropListF!SortBy
so   "ORDER BY " & Forms!PropListF!SortBy & ""

Try this (I think it works):

SQL = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
          "FROM PropT " & _
          "WHERE YEAR(BidDate)= " & YearCombo.Column(3) & " " & _
          "AND OfficeName= """ & OfficeNameCombo.Column(2) & """ " & _
          "ORDER BY " & Forms!PropListF!SortBy & ""

.

	Alex Hedley @Reply 5 years ago
Just to complicate things I usually use `Chr(34)`, the double quote character `"` instead of double double quotes as sometimes it's easier to see where they should start and end.

	Richard Rost @Reply 5 years ago
Scott's SQL looks good. It's syntactically correct. If the results don't work you may need to cast your year value to an integer. `"WHERE YEAR(BidDate)= " & CInt(YearCombo.Column(3)) & " " & _` But that shouldn't be necessary. And you don't need the little "" at the very end. :)

	Scott Axton @Reply 5 years ago
FYI - If you're interested in learning more about the StatusBox that Adam and I spoke about Richard covers it here: Customer Contact Template

Brent Rinehart OP @Reply

5 years ago

Thank You guys for all the input, one big issue I had was I put list values into the table design (I think this was a no no) no I have it all working from a table for the combo boxes values. Looks like I got it working except for Null values in the OfficeNameCombo If they select a year and show all offices  it doesnt work but if they select a year and a particular office it works. I think al I need now is how to included all value in OfficeName including Null Values

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & OfficeNameCombo.Column(0) & _
    " ORDER BY " & Forms!PropListF!SortBy

	Adam Schwanz @Reply 5 years ago
You can make it give null's a value with the NZ function. NZ Function

Brent Rinehart OP @Reply

5 years ago

I tried the NZ if I am using right but still when I select a year and select all offices I get nothing.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & Nz(OfficeNameCombo.Column(0)) & _
    " ORDER BY " & Forms!PropListF!SortBy

I create a status box and the SQL shows this, It looks right to me?

SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName FROM PropT WHERE YEAR(BidDate)=2020 AND OfficeName =0 ORDER BY EnteredDate DESC

	Alex Hedley @Reply 5 years ago
`=NZ( value, valueIfNull )`

	Brent Rinehart OP @Reply 5 years ago
HI Alex I tried Nz(OfficeNameCombo.Column(0)) with no luck I tried giving a a value if null, still no luck Nz(OfficeNameCombo.Column(0), 0)

Adam Schwanz @Reply

5 years ago

what about

If IsNull(OfficeNameCombo) Then
  'exit sub or msgbox, do something or do your same code with a where clause that doesn't include the officename criteria
Else
BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    "AND OfficeName =" & OfficeNameCombo.Column(0) & _
    " ORDER BY " & Forms!PropListF!SortBy
End If

	Kevin Robertson @Reply 5 years ago
Have you tried using an If block? If Not IsNull(ComboBoxName) then RowSource if value... Else RowSource if no value... End if

	Kevin Robertson @Reply 5 years ago
And Adam got there first. Lol :)

Brent Rinehart OP @Reply

5 years ago

I think we got it guys thank you it seems to be working, I was going to pull the rest of my hair out.
If IsNull(OfficeNameCombo) Or OfficeNameCombo = 0 Then
   BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=" & YearCombo.Column(2) & " " & _
    " ORDER BY " & Forms!PropListF!SortBy
Else
    RequeryBidList
End If

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View