[RESOLVED] Filtering recordsets. Help please!

To filter the recordsets with 2 variables (date and user) i use this code:

Code:

---

Set mrs = New ADODB.Recordset
With mrs
.ActiveConnection = cnn
.Source = "Select * From TABLE"
.Open "select F1,DATE,F3,USER,F5 from TABLE"
End With

mrs.Filter = "DATE='" & txtDate.Text & "' and USER='" & txtUser.Text & "'"

Set MSHFlexGrid1.Recordset = mrs

---

Now I want to filter recordsets betwen dates. I mean: "from txtData1 to txtData2 and txtUser too". How can i make this? Can anybody help me please? Thnx.

Have you tried this yet?

Code:

---

mrs.Filter = "DATE Between '" & txtDate1.Text & "' And '" & txtDate2.Text & "'"

---

or this...

Code:

---

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'"

---

No I didnt. What about the USER ?

What about it? :confused:

Quote:

---

Originally Posted by RhinoBull

What about it? :confused:

---

I mean to filter recordsets 1: Betwen dates and 2: User.
Hope i have myselfe clear.

So add the "user filter" (as you already did) to what I posted... :confused:

Can u give me the full code pls?

I used this code:

Code:

---

mrs.Filter = "DATE Between '" & txtDate1.Text & "' And '" & txtDate2.Text & "'" And "USER='" & txtUser.Text & "'"

---

or

Code:

---

'mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'" And "USER='" & txtUser.Text & "'"

---

and it tells me: Error13. Type mismatch. Where is my error?

P.S. In my database the data type of the field DATE is text. Is this the error?

When i use

Code:

---

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'"

---

it works fine but when I use

Code:

---

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'" And "USER='" & txtUser.Text & "'"

---

it tells me type mismatch. Where is the problem :(

Quote:

---

Originally Posted by ginothegodfather

Code:

---

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'" And "USER='" & txtUser.Text & "'"

---

it tells me type mismatch. Where is the problem

---

You have some extra quotes in the string

Code:

---

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "'  And  USER = '" & txtUser.Text & "'"

---

P.S. Your query actually works on the date filtering? You mentioned the db date field is text, is it formatted like yyyymmdd? Otherwise, I would imagine any correct filtering is just coincidence/lucky.

Highlighted "And" must be part of a your entire string instead:

mrs.Filter = "DATE >= '" & txtDate1.Text & "' And DATE <= '" & txtDate2.Text & "' And USER = '" & txtUser.Text & "'"

Your problem is here:


"'" And "USER='"

Try This:
MsgBox "'" And "USER='"

It gives me a type mismatch.

What database is it?

You should not be using a reserved word as a field name. Place square brackets around it so its not interpreted as a literal.

mrs.Filter = "([DATE] >= '" & txtDate1.Text & "' And [DATE] <= '" & txtDate2.Text & "') And ([USER] = '" & txtUser.Text & "')"