Help needed... (Search thru datagrid)

[benjaminhan]

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

[manoshailu]

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

[benjaminhan]

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

[AndreRet]

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

[manoshailu]

hi,

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

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

Have a nice day

Shailaja :)

[AndreRet]

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

[benjaminhan]

Hi All,

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

Regards,
B.H