[RESOLVED] Filtering recordsets. Help please!

[ginothegodfather]

To filter the recordsets with 2 variables (date and user) i use this 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.

[RhinoBull]

Have you tried this yet?

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

or this...

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

[ginothegodfather]

No I didnt. What about the USER ?

[RhinoBull]

What about it?

[ginothegodfather]

What about it?

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

[RhinoBull]

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

[ginothegodfather]

Can u give me the full code pls?

[ginothegodfather]

I used this code:

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

or

'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?

[ginothegodfather]

When i use

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

it works fine but when I use

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

it tells me type mismatch. Where is the problem

[LaVolpe]

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

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.

[RhinoBull]

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

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

[Zach_VB6]

Your problem is here:


"'" And "USER='"

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

It gives me a type mismatch.

[RobDog888]

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 & "')"