Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2014
    Posts
    7

    Unanswered: Query By Form Question

    You've helped me get over some tough hurdles so I'm going to try reaching out for help once again. I am creating a form to generate a query. We have quite a few date fields where we are tracking the progress of some contract documents. I would like the form to give them an option to run the query showing =, <>, <=, >=, In Null and Not Is Null. I want the user to be able to run a query such as DateA = Not is Null but DateB = Is Null. OR select DateA = xx/xx/xx.

    I tried just referencing the control within the query but it didn't retrieve any records. Any guidance is greatly appreciated.

    Thanks,
    Kelly

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If there are many data fields, the easiest way I know consists in using a set of tables to store the different elements of the query, like this:
    a) One table will hold the data information used to assemble the query:
    Code:
    CREATE TABLE [Tbl_Filters]
        ( [Field_Name] TEXT(127) NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [Data_Type] INTEGER NULL,
          [LinkOperator_ID] INTEGER NULL,
          [Operator_ID] INTEGER NULL,
          [Filter_Level] INTEGER NULL,
          [Control_Value] TEXT(255) NULL
        );
    b) Several lookup tables will be used in conjuction with Tbl_Filters:

    Tbl_DataTypes holds the different data types. This will be used to determine the default operator for each data type (= for a numeric value, Like for a text, etc.)
    Here is a sample of what I use:
    Code:
    ID	SQL_ID	Name	DefaultOperatorID
    0	0	dbUnknown	0
    1	11	dbBoolean	5
    2	0	dbbyte		5
    3	0	dbInteger	5
    4	0	dblong		5
    5	0	dbCurrency	5
    6	0	dbSingle	5
    7	0	dbDouble	5
    8	135	dbDate		5
    9	0	dbBinary	0
    10	202	dbText		8
    11	0	dbLongBinary	0
    12	0	dbMemo		8
    15	0	dbGUID		5
    17	0	dbVarBinary	0
    18	0	dbChar		8
    19	6	dbNumeric	5
    20	0	dbDecimal	5
    21	0	dbFloat		5
    22	0	dbTime		5
    23	0	dbTimeStamp	5
    Note: the column SQL_ID is not mandatory. I use it to correlate certain data types to their SQL Server counterparts.
    Code:
    CREATE TABLE [Tbl_DataTypes]
        ( [ID] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [SQL_ID] INTEGER NULL,
          [Name] TEXT(50) NULL,
          [DefaultOperatorID] INTEGER NULL
        );
    Tbl_LinkOperators contains the link operators ( And & Or). These operators are used to assemble 2 conditions (e.g. Active = True And Quantity > 0)
    He is a sample of it's contents:
    Code:
    SysCounter LinkOperator
    1	    AND
    2	    OR
    Code:
    CREATE TABLE [Tbl_LinkOperators]
        ( [SysCounter] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [LinkOperator] TEXT(5) NULL
        );
    The table Tbl_Operators contains all available operators to build the conditions. It also specifies which operator can be used with which data type and the minimum nuber of arguments needed. Here is a sample of such a table:
    Code:
    SysCounter	Operator	DataType_Mask	Casting_Mask	Arguments
    1		<		14		0		1
    2		<=		14		0		1
    3		>		14		0		1
    4		>=		14		0		1
    5		=		15		0		1
    6		<>		15		0		1
    7		*		14		12		-1
    8		Like		14		12		1
    9		Not Like	15		0		1
    10		Is Null		15		0		0
    11		Is Not Null	14		0		0
    12		Between		15		0		2
    13		Not Between	14		0		2
    14		IN		14		0		1
    15		Not IN		14		0		1
    Code:
    CREATE TABLE [Tbl_Operators]
        ( [SysCounter] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [Operator] TEXT(15) NULL,
          [DataType_Mask] TINYINT NULL,
          [Casting_Mask] TINYINT NULL,
          [Arguments] INTEGER NULL
        );
    The attached picture file #1 represents the relationships among these tables.

    c) From there, you create an interface that allows the user to insert values in the filter table (see attached pictures #2 and #3).

    d) Now, when the data form is in Filter mode, a double-click in a textbox, opens the filter mask (see attached picture #4).
    When the user finishes entering criteria, a procedure is called to analyse the contents of Tbl_Filter and compose the query from its contents. for this, you open a Recordset based on the following query:
    Code:
    SELECT Tbl_Filters.Field_Name, Tbl_Filters.Control_Value, Tbl_Operators.Operator, Tbl_LinkOperators.LinkOperator
    FROM  (Tbl_Filters INNER JOIN Tbl_Operators ON Tbl_Filters.Operator_ID = Tbl_Operators.SysCounter) INNER JOIN
           Tbl_LinkOperators ON Tbl_Filters.LinkOperator_ID = Tbl_LinkOperators.SysCounter
    WHERE  Tbl_Filters.Control_Value Is Not Null;
    Then you read the Recordset row by row and compose the SQL expression of the query:
    Code:
    Function CreateQuery(ByVal TableName As String) As String
    
        Const c_SQL As String = "SELECT Tbl_Filters.Field_Name, Tbl_Filters.Control_Value, Tbl_Operators.Operator, Tbl_LinkOperators.LinkOperator, Tbl_Filters.Data_Type " & _
                                "FROM  (Tbl_Filters INNER JOIN Tbl_Operators ON Tbl_Filters.Operator_ID = Tbl_Operators.SysCounter) INNER JOIN " & _
                                       "Tbl_LinkOperators ON Tbl_Filters.LinkOperator_ID = Tbl_LinkOperators.SysCounter " & _
                                "WHERE  Tbl_Filters.Control_Value Is Not Null;"
        Const c_Boolean As Long = 1
        Const c_Date As Long = 8
        Const c_Text As Long = 10
        Const c_Memo As Long = 12
        
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(strSQL) > 0 Then strSQL = strSQL & " " & rst!LinkOperator & " "
                strSQL = strSQL & rst!Field_Name & " " & rst!Operator & " "
                Select Case rst!Data_Type
                    Case c_Text, c_Memo: strSQL = strSQL & "'" & rst!Control_Value & "'"
                    Case c_Date:         strSQL = "#" & Format(rst!Control_Value, "yyyy-mm-dd") & "#"
                    Case c_Boolean:      strSQL = strSQL & CBool(rst!Control_Value)
                    Case Else:           strSQL = strSQL & CStr(rst!Control_Value)
                End Select
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        CreateQuery = "SELECT * FROM " & TableName & "WHERE " & strSQL & ";"
    
    End Function
    e) When you want to apply the filter at the form level, you can use a VBA expression, such as:
    Code:
    Me.RecordSource = CreateQuery("TableName")
    Attached Thumbnails Attached Thumbnails Filter_Schema.jpg   FilterMask_1.jpg   FilterMask_2.jpg   Open_Filter.jpg  
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    7
    Thank you very much! You gave me lots of good information and tools. Let me see if I can put this into action.

    Kelly

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Sep 2014
    Posts
    7
    Let me ask this question first to clear up any confusion I have regarding the NULL values. If I was going to have a simple combo box that gave the user a Yes/No or Received/Not Received option. How would I set up my query so that a response of "Yes" or "Received" generated a "Not Is Null" value for my query? And a "Is Null" value if "No" or "Not Received" option was selected. I want to make sure my initial request wasn't over complicating a simple query design.

    Thanks!
    Kelly

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The model I proposed does not work that way. If Is Null or Is Not Null is selected in the Operators combo (the second one on FilterMask_1.jpg), then the Value textbox on the right of the same picture should be disabled because if you examine the contents of Tbl_Operators you'll see that the Arguments column is set to zero (i.e. this operator does not take any argument). That's the job of the filter mask (SF_FilterMask) to check which number of argument(s) (0, 1, 2) is/are required.
    Have a nice day!

Posting Permissions

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