Hi, All

I am trying to do 2 level filter on datagrid. First I search the datagrid for same customer number. Than search again by date.
The problem I encounter is the first search works just fine (it return me records of a particular customer no.). But when it comes to the second search, the datagrid return to it original state. As to speak the whole list of customer.

Is there a way to keep the datagrid remains in it current state (after the first search.)?

Below is my code....

Public Sub search()
With Adodc1.Recordset
.Filter = "CUST_NO = '" & txtCustomer.Text & "'"
If .EOF Then
MsgBox "Item " + txtCustomer.Text + " Not found"
.Requery
txtCustomer.SetFocus
Else
.MoveFirst
End If
End With
End Sub

The 2nd search code is the same except it filter's criteria.

Thank in advance

Regards
B.H

Hi,
As you said the same code has been used for second code but the difference is filter correct. I think u forgot include the customer filter thats y u getting all customer details for the select date. U can do small changes as mentioned below to overcome this problem. I think it may help u.

With Adodc1.Recordset
[B]If txcustomer.Text<>"" then
           .Filter = "CUST_NO = '" & txtCustomer.Text & "' and CUST_DATE = '" & txtDate.Text & "'"
    Else
                .Filter = "CUST_DATE = '" & txtDate.Text & "'"
    End If[/B]        If .EOF Then
            MsgBox "Item " + txtCustomer.Text + " Not found"
            .Requery
            txtCustomer.SetFocus
        Else
            .MoveFirst
        End If
End With

While u posting the code use the code tag to differentiate the code from message.

Have a nice Day
Shailaja :)

Hi, All

I am trying to do 2 level filter on datagrid. First I search the datagrid for same customer number. Than search again by date.
The problem I encounter is the first search works just fine (it return me records of a particular customer no.). But when it comes to the second search, the datagrid return to it original state. As to speak the whole list of customer.

Is there a way to keep the datagrid remains in it current state (after the first search.)?

Below is my code....

Public Sub search()
With Adodc1.Recordset
.Filter = "CUST_NO = '" & txtCustomer.Text & "'"
If .EOF Then
MsgBox "Item " + txtCustomer.Text + " Not found"
.Requery
txtCustomer.SetFocus
Else
.MoveFirst
End If
End With
End Sub

The 2nd search code is the same except it filter's criteria.

Thank in advance

Regards
B.H

Hi Shailaja

Thank for your reply... It works. Thank you, Shailaja.
But is it possible to get a range of date? For example the result return me a list of data that appear within a period of time.

I try:

DOC_Date between '" & fromDate & "' and '" & toDate & "'

But is lead to an Error.

Regards
B.H

You would be looking at something like:

rsAuthorise.Open "SELECT * FROM yourfieldname WHERE DateSold >= DateValue('" & txtdate.text & "')" & " ORDER BY yournamefieldname", cnConnection, adOpenStatic, adLockOptimistic 
OR ------------------------------->
With Adodc1.Recordset
If txcustomer.Text<>"" then
           .Filter = "CUST_NO = '" & txtCustomer.Text & "' and CUST_DATE >= DateValue('" & txtdate.text & "')" 
    Else
                .Filter = "CUST_DATE = DateValue('" & txtdate.text & "')" 
    End If        If .EOF Then
            MsgBox "Item " + txtCustomer.Text + " Not found"
            .Requery
            txtCustomer.SetFocus
        Else
            .MoveFirst
        End If
End With

hi,

Instead of using between use >=,<= as like below

DOC_Date >='" & fromDate & "' and DOC_Date  <='" & toDate & "'

Have a nice day

Shailaja :)

If you use the DateVAlue() function, you will get more precise results back from that. It all depends on what your needs are.

Hi All,

Thank for all the replies. Very much appreciated.
Problem solved...:)

Regards,
B.H

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.