Yes/No checkbox showing as -1/0 once filter is applied.

[Mayhem9]

I have a form based filter that that will show "Yes" or "No" for a checkbox but this changes to "-1" or "0" once any on the combo boxes used to set the filters are used.

On initial loading:

Once the manufacturer is selected (same with any field selected):

The filter was created by MajP for another database that I use and it has been adapted to suite this one. The columns that are shown are displayed in a list box, which has the various columns call as such

SELECT tblTool_Log.ToolID, tblTool_Log.ManufacturerID, tblTool_Log.LocationID, tblTool_Log.CategoryID, tblTool_Log.SubCategoryID, tblTool_Log.YearID, tblTool_Log.FullYear, tblTool_Log.Lot, tblTool_Log.SourceID, tblTool_Log.Acquired, tblTool_Log.Quantity, PLAINTEXT(tblTool_Log.Description) AS DESCRIPTION, tblTool_Log.StatusID FROM tblTool_Log ORDER BY tblTool_Log.ToolID, tblTool_Log.Acquired, tblTool_Log.SourceID, tblTool_Log.ManufacturerID, tblTool_Log.YearID, tblTool_Log.CategoryID;

There is also a custom class module and whenever I have been adding new fields, I have simply copied existing fields and modified them until I can get it to work. I am sure that I need to format something to show -1 as Yes and 0 as No but I cannot see where I might do that. I am unsure which code is going to be helpful to include and don't want to simply dump every line in here. Please let me know what additional info I need to provide.

In the table (Tool_Log), the FullYear field is set to Yes/No and in the Form in which I input data, this is simply a check box that sits adjacent to the Year field.

Any help would be greatly appreciated. Thank you.

 

[Andrzejek]

You may simple try:

SELECT ToolID, ManufacturerID, LocationID, CategoryID, SubCategoryID,
YearID, [blue]IIF(FullYear = 0, "No", "Yes") As FullYear[/blue], Lot, SourceID, Acquired, Quantity,
PLAINTEXT(Description) AS DESCRIPTION, StatusID
FROM tblTool_Log
ORDER BY ToolID, Acquired, SourceID, ManufacturerID, YearID, CategoryID;

You may need to use single quotes around 'No' and 'Yes'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Mayhem9]

Thanks Andy,

Unfortunately it didn't work. Without the tblTool_Log. prefix, I get a circular reference error. Placing your IF statement into the original code resolves this error but I still get the numerical values as soon and I use any of the filter functionality.

 

[Andrzejek]

Could you show your code for your 'filter functionality'?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Mayhem9]

Thanks Andy,

I didn't actually code the FullYear (4 Digits), as it is not a field that I wanted to search by or to sort the column. All I wanted to do was have it display in the filter results. It isn't an important field by itself but rather in conjunction with the information in other fields. However, it is important in providing an quick reference of the details present on the stamping on the item.

Here is the code behind the form:

'form filter code by MajP from Tek-Tips ([URL unfurl="true"]http://www.tek-tips.com)[/URL]

Option Compare Database
Public fslTools As FilterSortListBox
Public blnSelect As Boolean

'sorts search results based upon the column heading
Private Sub cmdAcquired_Click()
fslTools.SortList ("Acquired, SourceID")
End Sub

Private Sub cmdDesc_Click()
  fslTools.SortList ("Description")
End Sub

Private Sub cmdFilter_Click()
  Me.Visible = False
End Sub

Private Sub cmdFullYear_Click()
  fslTools.SortList ("FullYear")
End Sub

Private Sub cmdID_Click()
fslTools.SortList ("ToolID")
End Sub

Private Sub cmdLocation_Click()
  fslTools.SortList ("LocationID")
End Sub

Private Sub cmdLot_Click()
  fslTools.SortList ("Lot")
End Sub

Private Sub cmdMan_Click()
  fslTools.SortList ("ManufacturerID")
End Sub

Private Sub cmdQty_Click()
fslTools.SortList ("Quantity")
End Sub

Private Sub cmdSelect_Click()
  blnSelect = True
  Me.Visible = False
End Sub

Private Sub cmdSource_Click()
    fslTools.SortList ("SourceID, SubCategoryID,YearID")
End Sub

Private Sub cmdSubCat_Click()
    fslTools.SortList ("SubCategoryID,YearID")
End Sub

Private Sub cmdYear_Click()
  fslTools.SortList ("YearID")
End Sub

Private Sub cmdCategory_Click()
  fslTools.SortList ("CategoryID, SubCategoryID,YearID")
End Sub

Private Sub cmdSubCategory_Click()
  fslTools.SortList ("SubCategoryID, YearID")
End Sub


Private Sub Form_Activate()
DoCmd.Maximize
End Sub

Private Sub Form_Close()
  resetValues
End Sub
Public Sub resetValues()
  On Error GoTo errlbl
  Me.qMan1.Value = ""
  Me.qCat1.Value = ""
  Me.qSub1.Value = ""
  Me.qLoc1.Value = ""
  Me.qSrc1.Value = ""
  Me.qYear1.Value = ""
  Me.qAcq1.Value = ""
  Me.qSts1.Value = ""
  Exit Sub
errlbl:
  If Err.Number = 2467 Then
    Exit Sub
  Else
    MsgBox Err.Number & Err.Description
  End If
End Sub

Private Sub Form_GotFocus()
DoCmd.Maximize
End Sub

Private Sub Form_Load()
  Set fslTools = New FilterSortListBox
  fslTools.Initialize Me.lstSearch
  resetValues
End Sub

Private Sub lstSearch_AfterUpdate()

End Sub

Private Sub qAcq1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qFullYear1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qLot1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qMan1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qYear1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qCat1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qLoc1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qSrc1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qSub1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub qSts1_AfterUpdate()
  fslTools.FilterList (getFilter)
End Sub

Private Sub Reset_Click()
  resetValues
  fslTools.unFilterList
End Sub

Private Sub Search_Click()
  Me.Visible = False
End Sub

Private Sub ExitForm_Click()
On Error GoTo Err_ExitForm_Click
    DoCmd.Close acForm, Me.Name
Exit_ExitForm_Click:
    Exit Sub
Err_ExitForm_Click:
    MsgBox Err.Description
    Resume Exit_ExitForm_Click
End Sub

Public Function getFilter() As String
 ' On Error GoTo errLable
  Dim strType As String
  Dim strManufacturer As String
  Dim strSerial As String
  Dim strSet As String
  Dim strSubCategory As String
  Dim strLocation As String
  Dim strSource As String
  Dim strYear As String
  Dim Acquired As String
  Dim strStatus As String
  Dim andOR As String
  Dim removeEnd As Integer
  Dim strFullYear As String
  
  If Not blnSelect Then
    
    If Me.framAndOr.Value = 0 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
      
    If Not Trim(Me.qMan1 & " ") = "" Then
        strManufacturer = "[ManufacturerID] = '" & qMan1 & "'" & andOR
    End If
    
    If Not Trim(Me.qCat1 & " ") = "" Then
        strType = "[CategoryID] = '" & qCat1 & "'" & andOR
    End If

    If Not Trim(Me.qSub1 & " ") = "" Then
        strType = "[SubCategoryID] = '" & qSub1 & "'" & andOR
    End If
  
    If Not Trim(Me.qLoc1 & " ") = "" Then
        strLocation = "[LocationID] = '" & qLoc1 & "'" & andOR
    End If
    
    If Not Trim(Me.qSrc1 & " ") = "" Then
        strSource = "[SourceID] = '" & qSrc1 & "'" & andOR
    End If
    
    If Not Trim(Me.qYear1 & " ") = "" Then
        strYear = "[YearID] = '" & qYear1 & "'" & andOR
    End If
    
    If Not Trim(Me.qAcq1 & " ") = "" Then
        strYear = "[Acquired] = " & SQLDate(Me.[qAcq1]) & " " & andOR
    End If
    
    If Not Trim(Me.qSts1 & " ") = "" Then
        strSource = "[StatusID] = '" & qSts1 & "'" & andOR
    End If
      
    getFilter = strType + strManufacturer + strLocation + strSubCategory + strSource + strYear
    getFilter = Left(getFilter, Len(getFilter) - removeEnd)
    'Debug.Print getFilter
    
  Else
    If Not IsNull(lstSearch) Then
      getFilter = "[ToolID] = " & Me.lstSearch
    End If
  End If
  
  'You may comment this out
'  Commented out 15/4/21 DW
  'Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLable:
  MsgBox Err.Number & "  " & Err.Description
End Function

Here is the Class Module:

Option Compare Database
Option Explicit

'Class Module Name: FindAsYouListBox
'Purpose: Turn any Listbox into a "Find As You Type"  listbox
'Created by: MajP

Private WithEvents mListbox As Access.ListBox
Private WithEvents mForm As Access.Form
Private mFilterString As String
Private mSortString As String
Private mRsOriginalList As DAO.Recordset
Private Sub mListBox_AfterUpdate()
  'Call unFilterList
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub

Public Sub FilterList(FilterString As String)
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = FilterString
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mListbox.Recordset = rsTemp
    mListbox.Selected(0) = True
    mListbox.Value = mListbox.Column(0)
  Else
    MsgBox "No Records Found"
    Call unFilterList
  End If
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify filter string is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Public Sub unFilterList()
  On Error GoTo errLable
  Set mListbox.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mListbox = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub Initialize(theListBox As Access.ListBox)
   On Error GoTo errLabel
  If Not theListBox.RowSourceType = "Table/Query" Then
    MsgBox "This class will only work with a ListBox that uses a Table or Query as the Rowsource"
    Exit Sub
  End If
  Set mListbox = theListBox
  Set mForm = theListBox.Parent
  mForm.OnCurrent = "[Event Procedure]"
  mListbox.AfterUpdate = "[Event Procedure]"
 Set mRsOriginalList = mListbox.Recordset.Clone
 Exit Sub
errLabel:
 MsgBox Err.Number & " " & Err.Description
End Sub

Public Sub SortList(SortString As String)
  Dim rs As DAO.Recordset
  Set rs = mListbox.Recordset
  rs.Sort = SortString
  Set mListbox.Recordset = rs.OpenRecordset
  Set rs = mRsOriginalList
  rs.Sort = SortString
  Set mRsOriginalList = rs.OpenRecordset
End Sub

Thank you for taking the time to look at this.

 

[Andrzejek]

Are all your IDs (ManufacturerID, CategoryID, SubCategoryID, LocationID, etc.) Strings/Text? I ask because you put single quotes around them.

To set the value of your getFilter (strSQL in my case) I would do:

Dim strSQL As String

strSQL = "SELECT ToolID, ManufacturerID, LocationID, CategoryID, SubCategoryID,
YearID, IIF(FullYear = 0, "No", "Yes") As FullYear, Lot, SourceID, Acquired, Quantity,
PLAINTEXT(Description) AS DESCRIPTION, StatusID
FROM tblTool_Log[blue] Where 1 + 1 = 2 AND [/blue]"

    If Not Trim(Me.qMan1 & " ") = "" Then[blue]
        strSQL = strSQL & [/blue]" ManufacturerID = '" & qMan1 & "'" & andOR
    End If
    
    If Not Trim(Me.qCat1 & " ") = "" Then[blue]
        strSQL = strSQL & [/blue]" CategoryID = '" & qCat1 & "'" & andOR
    End If
    [red]....[/red]
[blue]
strSQL = strSQL & [/blue]" ORDER BY ToolID, Acquired, SourceID, ManufacturerID, YearID, CategoryID"

And here you have your SQL - with or without any filtering. And your logic goes thru the same code/logic no matter what.

BTW - if you do not use Spaces or reserved words in the names of your fields, you do not need to enclose fields' names in [ ]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Mayhem9]

Thanks Andy,

Yes, those IDs are all strings

Whenever I try to insert the code, it turns this section of text red:

YearID, IIF(FullYear = 0, "No", "Yes") As FullYear, Lot, SourceID, Acquired, Quantity,
PLAINTEXT(Description) AS DESCRIPTION, StatusID
FROM tblTool_Log Where 1 + 1 = 2 AND "

Then it highlights the top line in blue and returns a Compile error: Syntax Error. I placed the Dim command in with the other public functions and then inserted the aforementioned code into the next section, above the line that reads:

    If Not Trim(Me.qMan1 & " ") = "" Then
        strManufacturer = "[ManufacturerID] = '" & qMan1 & "'" & andOR
    End If

I fear that in spite of your help that I am out of my depth here.

 

[Andrzejek]

Well, I skip the continuation marks in my string. :-(
It should be something like:

[pre]
strSQL = "SELECT ToolID, ManufacturerID, LocationID, CategoryID, SubCategoryID, " _
& " YearID, IIF(FullYear = 0, 'No', 'Yes') As FullYear, Lot, SourceID, Acquired, Quantity, " _
& " PLAINTEXT(Description) AS DESCRIPTION, StatusID " _
& " FROM tblTool_Log Where 1 + 1 = 2 AND "
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson