arbitel arbitel - 1 month ago 15
SQL Question

How to get MS Access Listbox to show ALL values on YesNo Field

I have a Listbox in Access that shows a list of seasonal employee names. It's row source is:

SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ", " & [FirstName] AS Expr1, tblLeaders.OnPay
FROM tblLeaders
ORDER BY tblLeaders.LastName;


It shows the names in a listed sorted by Last name and presented as: "[LastName], [FirstName]"

On the table [tblLeaders], there is a YesNo field called [OnPay].

My goal is to have a checkbox or combobox that can filter the names in the ListBox by either:

Option 1: ALL (show all OnPay values (yes or no)

Option 2: Active (show only OnPay values set to 'yes')

I've attempted to use the triple state of a checkbox, but that didn't work. I then tried to use a IIF statement to find "Yes" and "No" values if a combobox was set to all, but I kept receiving errors.

Does anyone have any simple ideas how to solve this?

UPDATE
Thanks Johanness for this answer. Below is the working vba code I added

Private Sub chkbxShowInactiveLeaders_Click()
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String

sql1 = "SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ', ' & [FirstName] AS Expr1, tblLeaders.OnPay FROM tblLeaders "
sql2 = "WHERE tblLeaders.OnPay=TRUE "
sql3 = "ORDER BY tblLeaders.LastName;"

If Me.chkbxShowInactiveLeaders.Value = True Then
Me.lstbxSelectLeader.RowSource = sql1 & sql3
Else
Me.lstbxSelectLeader.RowSource = sql1 & sql2 & sql3
End If

Me!lstbxSelectLeader.Selected(0) = True

End Sub

Answer

As you set vba as tag I assume you are familiar with vba. Then you could use a checkbox and create a OnClick-Action that changes the RowSource of the Listbox. That should be simple enough; something like:

Private Sub CBShowAll_Click()
  Dim sql1 as String
  Dim sql2 as String
  Dim sql3 as String

  sql1="SELECT tblLeaders.LeaderID, tblLeaders.FirstName, tblLeaders.LastName, [LastName] & ", " & [FirstName] AS Expr1, tblLeaders.OnPay FROM tblLeaders "
  sql2="WHERE tblLeaders.OnPay=TRUE "
  sql3="ORDER BY tblLeaders.LastName;" 

  If me.CBShowAll.Value Then
    me.Leaderlist.RowSource= sql1 & sql3
  Else
    me.Leaderlist.RowSource= sql1 & sql2 & sql3
  End If
  Me.Leaderlist.Requery 'Not sure if this is necessary
End Sub