I have a table which has a yes/no field, in the table everything displays as either Yes or No, unless you click on it to edit it, whereby No become 0 and Yes becomes -1.
This isn't a big problem as users can only access forms. On the input form this is a check box, checked for Yes and unchecked for No. This used to be a drop down selection box of Yes and No and the same problem was occuring, so I know the problem doesn't relate to the check box.
I have a second reporting form which has multiple combo boxes to filter down results in a subform. In the subform the field correctly displays as Yes and No, however, the filtering combobox for this field has the options 0 and -1.
The combobox is set up as Yes/No, the table is Yes/No and the results are Yes/No. There is no other formatting or input options that can be changed to tell the database this is a Yes/No field.
How can I get this combobox to display as Yes/No? It's causing a lot of confusion with users as they are having to select 0 or -1.
As a note, I've already tried the following:
Deleting the combobox and starting again.
Deleting the table and combo box and starting again.
Deleting all tables, queries and forms which use this field and starting again.
None have worked, this seems to be something Access is doing rather than an error in what I've done.
The filter form uses this code, which may be the cause:
Option Compare Database
Private m_colCombos As Collection
Private m_strFilter As String
Private Sub ApplyFilter(Optional ByVal Filter As String)
If Len(Filter) > 0 Then
FindRFQsubform.Form.Filter = Filter
FindRFQsubform.Form.FilterOn = True
FindRFQsubform.Form.Filter = ""
FindRFQsubform.Form.FilterOn = False
Private Function BuildFilter()
Const c_LinkOperator As String = " AND "
Dim ctl As Control
Dim m_strFilter As String
Dim strCriteria As String
m_strFilter = ""
For Each ctl In m_colCombos
If Not IsNull(ctl.Value) Then
If Len(m_strFilter) > 0 Then m_strFilter = m_strFilter & c_LinkOperator
strCriteria = ctl.Tag & " Like " & GetQuotedValue(ctl.Tag, ctl.Value)
m_strFilter = m_strFilter & strCriteria
Private Function GetQuotedValue(ByVal FieldName As String, ByVal Value As Variant) As String
Select Case FindRFQsubform.Form.RecordsetClone.Fields(FieldName).Type
Case dbMemo, dbText
GetQuotedValue = "'" & Value & "'"
Case dbDate, dbTime
GetQuotedValue = "#" & Format(Value, "mm/dd/yyyy hh:nn:ss") & "#"
GetQuotedValue = Value
Private Sub InitializeComboCollection()
Const c_SQL As String = "SELECT DISTINCT NULL FROM @T UNION SELECT DISTINCT @C FROM @T;"
Dim ctl As Control
Set m_colCombos = New Collection
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.HelpContextId = -4 Then
ctl.RowSource = Replace(Replace(c_SQL, "@T", "FindRFQ"), "@C", ctl.Tag)
ctl.AfterUpdate = "=BuildFilter()"
Private Sub Form_Open(Cancel As Integer)
Private Sub Exit_Click()
On Error GoTo Err_Exit_Click