Search form

[Falcon88]

hii all dears

based on this example i build a seperated filter form (without record source data) , to filter another form , but it does not filter that another form .
i use this code :

Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

      'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.CVZ) Then
        strWhere = strWhere & "(Forms![MainFrm]![InsurCmpName] = " & Me.CVZ & ") AND "
    End If
     If Not IsNull(Me.DateXz) Then
        strWhere = strWhere & "(format(Forms![MainFrm]![OrderDate],'m') = " & Me.DateXz & ") AND "
    End If
    'ByDatSearch
    If Not IsNull(Me.ByDatSearch) Then
        strWhere = strWhere & "(Forms![MainFrm]![OrderID] = " & Me.ByDatSearch & ") AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Forms![MainFrm].Filter = strWhere
        Forms![MainFrm].FilterOn = True
        Forms![MainFrm].OrderBy = "[OrderID] ASC"
        Forms![MainFrm].OrderByOn = True
    End If

 

[bastanu]

You don't need the Forms![MainForm]! in the strWhere construction; note that you need to wrap text (strings) in single quotes (or double doublequotes "") and dates with #. So if InsCompName is an alpha string you would have strWhere = strWhere & "([InsurCmpName] ='" & Me.CVZ & "') AND " and so on. For numeric values you leave them like you have them.

Cheers,

 

[MajP]

this function makes wrapping the delimiters much easier. CSQL

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

strWhere = strWhere & "([InsurCmpName] =" & csql(Me.CVZ) & ") AND "

 

[Falcon88]

You don't need the Forms![MainForm]! in the strWhere construction; note that you need to wrap text (strings) in single quotes (or double doublequotes "") and dates with #. So if InsCompName is an alpha string you would have strWhere = strWhere & "([InsurCmpName] ='" & Me.CVZ & "') AND " and so on. For numeric values you leave them like you have them.

Cheers,

thanks , dear plz note : i use filter form (frmFilter , it's record source="") to filter data on MainFrm that is another seperated form

 

[bastanu]

It doesn't matter if it is a different form, the passed strWhere variable would contain the controls on that form so no need to fully qualify them by adding the Forms![MainForm]. But the problem is probably not that but improper wrapping of the individual variables....

Cheers,