Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013

    Unanswered: Yes/No box displaying as 0/-1

    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
    Option Explicit
    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
        End If
    End Sub
    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
            End If
        Next ctl
        ApplyFilter m_strFilter
    End Function
    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") & "#"
            Case Else
                GetQuotedValue = Value
        End Select
    End Function
    Private Sub InitializeComboCollection()
        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()"
                m_colCombos.Add ctl
            End If
            End If
        Next ctl
    End Sub
    Private Sub Form_Open(Cancel As Integer)
    End Sub
    Private Sub Exit_Click()
    On Error GoTo Err_Exit_Click
    DoCmd.OpenForm "Switchboard"
        Exit Sub
        MsgBox Err.Description
        Resume Exit_Exit_Click
    End Sub

  2. #2
    Join Date
    Nov 2011


    I don't know the particulars but why not simply use this?
    Attached Thumbnails Attached Thumbnails YesNo.png  

  3. #3
    Join Date
    May 2013
    Brilliant thank you, it works now

  4. #4
    Join Date
    Nov 2011
    Your welcome, Good Luck With Your Project!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts