Return data type of value in unbound control

[PhynomAccess]

The purpose of my procedure is to build an SQL statement with the BuildCriteria function to filter a query. The criteria is supplied from unbound form controls on a form.



The procedure uses a 'Select Case...End Select' clause that cases the control type. The challenge is that a text box control can hold more than a string/text data type. Additionally, the control is not bound.

I have a text box control that stores a date. Therefore, an error is generated when the BuildCriteria([control], db data type = text (dbText), value) tries to pass a date data type to the function.

My question is how can I iterate through my unbound controls and return the data type of each, and use that type of building my SQL statement.

The procedure is included below:

Private Sub cmbSetFilter_Click()


'The following procedure was copied and modified from
'https://learn.microsoft.com/en-us/office/troubleshoot/access/build-dynamic-query-from-search-form


On Error Resume Next


Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
Dim strField As Field


'Initialize the WHERE clause variable
sWhereClause = " WHERE "


'Start the first part of the SELECT statement.
sSQL = "SELECT * FROM ActivityLog "


'This code prints the name of each control type listed in the case
'The case criteria identifies the type of control that is required to parse
'the BuildCriteria method.
'To use the test code, uncomment the following lines between
'the Test Code phrases.
'Test Code
'For Each ctl In Me.Controls
'   With ctl
'   Select Case .ControlType
'      Case acTextBox
'         Debug.Print .Name
'      Case acComboBox
'         Debug.Print .Name
'   End Select
'   End With
'Next ctl
'Test Code


'Loop through each control on the form to get its value
   For Each ctl In Me.Controls
      With ctl
      'Grab value from various Control types and concatenate those values for SQL statement
      Select Case .ControlType
         Case acTextBox
            .SetFocus
            strField = Form.ActiveControl.ControlSource
            Debug.Print strField
            'Build the WHERE clause
            'The If statement determines if the For loop has executed and within the loop criteria has
            'alread been assigned such that Else statement catches the clause after the first iteration
            'of the loop.
               If sWhereClause = " WHERE " Then
                  sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
               Else
                  sWhereClause = sWhereClause & " AND " & BuildCriteria(.Name, dbText, .Text)
               End If
               Debug.Print sSQL & sWhereClause
  
         Case acComboBox
            .SetFocus
            'Build the WHERE clause
            'The If statement determines if the For loop has executed and within the loop criteria has
            'alread been assigned such that Else statement catches the clause after the first iteration
            'of the loop.
            If sWhereClause = " WHERE " Then
               sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
            Else
               sWhereClause = sWhereClause & " AND " & BuildCriteria(.Name, dbText, .Text)
            End If
         End Select
      End With
   Next ctl


'Assign a text box to display the query that was created by the procedure
Me.txtSQL = sSQL & sWhereClause
Me![Activity Entry Datasheet].Form.RecordSource = sSQL & sWhereClause
Me![Activity Entry Datashett].Requery

[Gicu]

Maybe look into using the IsNumeric(), IsDate(), IsNull() functions to check the data types....

[Welshgasman]

This thread should get you started, if not all the way there?
https://www.access-programmers.co.uk...ilters.311743/