Search multiple fields simultaneously for values within a certain range

[Eugenio]

Hi there, I'm returning to this forum for the second time and I would like to say thanks for the great help provided. I've encountered a new problem now and hope that you will be able to help me again.

I'm currently implementing a container monitoring system in MSAccess07 and I'm using a multiple search function to sort containers. I use several unbound text boxes and a list box.The user type's in partial search criteria in one or several text boxes. Or if they don't key in anything it returns all records. The matches are returned in a listbox.

I will show the code for 1 text box just to show how it works.

This is the code for the search button:

Private Sub cmdShowCont_Click()
Dim MySQL As String, mycriteria As String, MyRecordSource As String
Dim argcount As Integer
Dim Tmp As Variant
mycriteria = " "
argcount = 0
MySQL = "SELECT * FROM qryContLife  WHERE "
 
Addwtf [textbox], "[Arrival Date]", mycriteria, argcount, "textbox"
 
Debug.Print mycriteria
If mycriteria = " " Then
mycriteria = "True"
End If
 
MyRecordSource = MySQL & mycriteria
Me![listbox].RowSource = MyRecordSource
If Me![listbox].ListCount = 0 Then
MsgBox " There are no containers with this criteria. Sorry ", 48
Me!cmdClear.SetFocus
Else
Me![listbox].SetFocus
End If
 
End Sub
 

This is the code for the sub which builds the SQL statement and through which the values are returned to the listbox:

Private Sub Addwtf(fieldvalue As Variant, fieldname As String, mycriteria As String, argcount As Integer, fieldo As String)
Dim wtf1 As String, wtf2, wtf3 As String
 
 If fieldvalue <> "" Then
 If argcount > 0 Then
     mycriteria = mycriteria & " and "
 
 End If
 
 Select Case fieldo
 
Case "textbox"
       wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
       mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
 
  argcount = argcount + 1
 End If
End Sub
 

The question is whether it is possible to make the search not for a specific date like the "Arrival Date" in this case (e.g. 01/12/2008) but for a specified range of dates like all values between 01/12/2008 and 01/01/2009. So that the code returns not just one value but all values in a specified range.

I hope I explained everything in a clear matter, if not please ask questions and I will try and make it more clear.

Thank you for spending your time of somebody else's problems.
Best regards, Eugene.

[Eugenio]

If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
   If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
      mycriteria = mycriteria _
        & " AND [BirthDate] Between #" _
        & Format(Me![txtBeginDate], "yyyy/mm/dd") _
        & "# And #" _
        & Format(Me![txtEndDate], "yyyy/mm/dd") & "# "
   End If
End If
 
 

Like this ? I cannot manage to retrieve the data to the listbox in this way though.