Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    48

    Unanswered: Dynamically add filter in Query attached with Form.

    Hi,

    I have created Form, which is using following query.
    In Form, all records get executed. I want to add dynamically some filter condition in query and then execute it in same Form.

    SELECT [HPO].[PVEND], Count(*) AS TimeTotalReceived,
    Sum([HPO].[PQORD]) AS TotalPartOrdered,
    Sum([HPO].[PQREC]) AS TotalPartReceived,
    Sum(IIf([HPO].[PQORD]<[GRGDT].[GDRQTY],1,0)) AS TimeMoreReceived,
    Sum(IIf([HPO].[PQORD]<[GRGDT].[GDRQTY],[GRGDT].[GDRQTY],0)) AS PartsMoreReceived,
    Sum(IIf([HPO].[PQORD]>[GRGDT].[GDRQTY],1,0)) AS TimeLessReceived,
    Sum(IIf([HPO].[PQORD]>[GRGDT].[GDRQTY],[GRGDT].[GDRQTY],0)) AS PartsLessReceived,
    Sum(IIf([HPO].[PQORD]=[GRGDT].[GDRQTY],1,0)) AS TimeExactReceived,
    Sum(IIf([HPO].[PQORD]=[GRGDT].[GDRQTY],[GRGDT].[GDRQTY],0)) AS PartsExactReceived,
    Sum(IIf([HPO].[PDDTE]<[GRGDT].[GDDTEA],1,0)) AS TimeEarlyReceived,
    Sum(IIf([HPO].[PDDTE]<[GRGDT].[GDDTEA],[GRGDT].[GDRQTY],0)) AS PartsEarlyReceived,
    Sum(IIf([HPO].[PDDTE]>[GRGDT].[GDDTEA],1,0)) AS TimeLateReceived,
    Sum(IIf([HPO].[PDDTE]>[GRGDT].[GDDTEA],[GRGDT].[GDRQTY],0)) AS PartsLateReceived,
    Sum(IIf([HPO].[PDDTE]=[GRGDT].[GDDTEA],1,0)) AS TimeOntimeReceived,
    Sum(IIf([HPO].[PDDTE]=[GRGDT].[GDDTEA],[GRGDT].[GDRQTY],0)) AS PartsOntimeReceived
    FROM HPO INNER JOIN GRGDT ON ([HPO].[PLINE]=[GRGDT].[GDORLN]) AND ([HPO].[PORD]=[GRGDT].[GDORDR])
    GROUP BY [HPO].[PVEND]
    ORDER BY [HPO].[PVEND];


    Is there any way to add Criteria(Date range) dynamically in QUERY attached(Data Source) with a FORM.
    I can not include 2 Date fields in my SELECT fields.

    I want to add Where condition like
    " Where GRGDT >= Form_Parameter1 and GRGDT <= Form_Parameter2 " in query dynamically

    Thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    there are two ways... use FILTER or change RECORDSET

    I recommend to change the recordset....

    so you open form with your standard recordset, then you hit the button, which opens another form (or not) where you select your criteria and then you hit OK. the code will change form's recordset and checks if forms' total number of filtered records > 0 if yes, then close your form where you selected criteria, if no then do some msgbox or return original recordset.

    Forms("myForm").Recordsource gives you your recordset
    Forms("myForm").Recordset.RecordCount gives you number of records selected


    you have to put together new recordset from existing recordset and stick before ORDER BY (in your special situation GROUP BY) your WHERE seq., it's pretty easy - use InStrRev Function and find word "ORDER BY" (or "GROUP BY" and inject before that your WHERE

    SELECT something FROM table WHERE something ORDER BY something

    in your situation

    SELECT something FROM table WHERE something GROUP BY something ORDER BY something




    small sample - it works with ORDER BY ....



    Option Compare Database
    Option Explicit


    Private Sub Command2_Click()
    Dim strOriginalSQL As String
    Dim strNewSQL As String
    Dim myWhere As String
    Dim intORDERBYPosition As Integer
    'this SUB does NOT look at situation when you already have WHERE in your
    'query, you have add another check....

    ' Reset Variables cauze you can use them again and again without leaving the form
    strOriginalSQL = ""
    strNewSQL = ""
    myWhere = ""
    intORDERBYPosition = 0

    ' the recordsource has to look like SELECT something FROM ....
    ' if cannot be just a table!!!

    ' this is my WHERE
    myWhere = " WHERE jFirstName = " & Chr(34) & "Jamess" & Chr(34)

    ' I use FORMS instead of ME because I expect that you will have a form not a button
    strOriginalSQL = Trim(Forms("myForm").RecordSource)

    If Right(strOriginalSQL, 1) = ";" Then
    ' this does not work for 100%
    strOriginalSQL = Left(strOriginalSQL, Len(strOriginalSQL) - 1)
    End If

    intORDERBYPosition = InStrRev(strOriginalSQL, " ORDER BY ")

    If intORDERBYPosition = 0 Then
    strNewSQL = strOriginalSQL & " " & myWhere
    Else
    strNewSQL = Left(strOriginalSQL, intORDERBYPosition - 1) & " " & _
    myWhere & " " & Mid(strOriginalSQL, intORDERBYPosition)
    End If
    Forms("myForm").RecordSource = strNewSQL

    If Forms("myForm").Recordset.RecordCount = 0 Then
    MsgBox "I cannot find any records!", vbExclamation
    Forms("myForm").RecordSource = strOriginalSQL
    End If

    End Sub
    Last edited by playernovis; 12-31-02 at 21:52.

  3. #3
    Join Date
    Oct 2002
    Posts
    48

    thanks.

    I used

    me.recordsource = "Select * from table1 where etc.."
    me.requery

    It works.
    Based on parameter selected, I am assiging new query to Me.Recordsource.

    Thanks.

    Sorry for delay. I have just started to work on that issue.

Posting Permissions

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