How to put 2 "filter box" in AND?

[PLA300]

hi everybody.
First of all sorry for my bad english.

I have a silly question but i can't resolve.


i have 2 textbox wich I'd like to use as a filter.
the boxs work perfectly one at time. but if i want to filter
using both I have to use a AND instruction. but i'm not able...
can someone be so kind to help me?
this is my code:

Private Sub box1_Change()
Dim rst As DAO.Recordset
   Dim strR As String
   Dim sFiltro As String
   strR = Me.box1.Text
   
   sFiltro = "field1 like '*" & strR & "*'"
    Me.Filter = sFiltro
    Me.FilterOn = True

 
   Me.box1.SelStart = 255
End Sub

Private Sub box2_Change()
Dim rst As DAO.Recordset
   Dim strR As String
   Dim sFiltro As String
   strR = Me.box2.Text
   
   sFiltro = "field2 like '*" & strR & "*'"
    Me.Filter = sFiltro
    Me.FilterOn = True

 
   Me.field2.SelStart = 255
End Sub

best regards

[pbaldy]

Try this type of thing:

"field1 like '*" & strR & "*' AND field2 like '*" & strR & "*'"

[PLA300]

first of all THANKS!
it doesn't works correctly:
It search the string I write in box1 in field1 an field2.
i need something of a little different: box1 have to search in field1, box2 have to search in field2 (but in the same time).

eg:
records:
white
greee 2010
blue 2010
blue 2011
white 2011

box1: blue AND box2:2011 (or 11)
have to show me:
blue 2011

best regards

[pbaldy]

If the second is numeric it would look like:


"field1 like '*" & strR & "*' AND field2 = " & OtherVariable

[PLA300]

I have 3 boxes, 2 are text one is numeric.
I wrote 2 boxes and used a numeric e.g. to explain it easier...

[pbaldy]

What does your effort look like? As you can see from my examples, text data types get surrounded by the single quotes, numeric types do not (and dates by # if it comes up).

[PLA300]

sorry i did not understand :-( (and google translator doesn't help me now...
can i attach my example file?
(i'm a little tired.. it's near 10 PM here)

[pbaldy]

I wanted to see your existing code. You should be able to post the db here.

[PLA300]

Thanks!
the green boxes are for write the strings...
i don't know if it could be useful: green boxes have the same "tag"

[PLA300]

goodmorning!
i found this solution...
not with an AND in boxes VBA but making an union with a query, then using another box (yellow) wich search in the new field.
(the old boxes have been renamed so old VBA is ignored).
It works but i'd like to add some code on change on green boxes that make the filter starts. Actually it start by a pression of enter (event)
this is my file.

[PLA300]

Some ideas? Are my files downloadable or understandable?
best regards

[pbaldy]

It is morning here, I'm just getting going. Have just looked at the file. This is not how I would do it, but if you change the sub on the yellow box from Private to Public you can call it from the after update event of the others with:

Call filtro_unione_Enter

You also have to add this at the beginning of the code to move focus to that textbox:

Me.filtro_unione.SetFocus

[PLA300]

Thank you!!!
Call filtro_unione_Enter is Private sub?
In what point i have to add: Me.filtro_unione.SetFocus ?
(anyway if you have a better idea is not a problem for me!, i'm here to learn).
have a nice day!

[pbaldy]

My tendency is to create dynamic SQL. Here's an example:

Public Function SearchBookings()
  Dim strSQL                  As String

  On Error GoTo ErrorHandler

  If IsNumeric(Me.txtResNum) Then
    strSQL = strSQL & " AND ResNum = " & Me.txtResNum
  End If

  If IsDate(Me.txtFromDate) And IsDate(Me.txtToDate) Then
    strSQL = strSQL & " AND ResDate Between " & Format(Me.txtFromDate, conJetDate) & " And " & Format(Me.txtToDate, conJetDate)
  ElseIf IsDate(Me.txtFromDate) Then
    strSQL = strSQL & " AND ResDate = " & Format(Me.txtFromDate, conJetDate)
  End If

  If Not IsNull(Me.cboCompany) Then
    strSQL = strSQL & " AND conCompany = '" & Me.cboCompany & "'"
  End If
  
  strSQL = "SELECT ResNum, FullName, PickupAddy, ResDateTime, Direction " _
         & "FROM qryAllRes " _
         & "WHERE 1=1" & strSQL _
         & " ORDER BY ResDateTime"

  Me.lstReservations.RowSource = strSQL

ExitHandler:
  Exit Function

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description & " in SearchBookings "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Function

Private Sub cboCompany_AfterUpdate()
  SearchBookings
End Sub

[PLA300]

i'm sure that your solution is better than mine but unfortunately i can't "speak" VBA and SQL so is too harder for me. :-( (i'm very sorry)
(i'm just able to copy and past some very short pieces of code and use it)

My solution can work? i'd like to use it for another problem but I need that yellow box filter is "active" when the form is open.

have a nice day!

update... I can't do it work without press enter on the yellow box.
(i tried to add setfocus as first row of all the code but in this case my box stop to work totally)
here is my file updated with Public sub as you told me.
have a nice day again and i hope to have a goodnight greetins from pedimunt :-)