Search multiple fields simultaneously with data ranges 1

[Eugenios]

Hello, I'm new to this forum and would like to greet everybody.

I'm working on a search function which is similar to the one given on this website provided here :

http://www.tek-tips.com/faqs.cfm?fid=1177

Only I currently have 5 textboxes for search and the results are returned to a listbox. This type of search retrieves all the values which contain the specified criteria. This is the code I have for the search button:

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 [myd1], "[Container Number]", mycriteria, argcount, "myd1"
Addwtf [myd2], "[OwnerCode]", mycriteria, argcount, "myd2"
Addwtf [myd3], "[Arrival Date]", mycriteria, argcount, "myd3"
Addwtf [myd4], "[Depot In Date]", mycriteria, argcount, "myd4"
Addwtf [myd5], "[Container Size]", mycriteria, argcount, "myd5"

Debug.Print mycriteria
If mycriteria = " " Then
mycriteria = "True"
End If

MyRecordSource = MySQL & mycriteria
Me![lstContList].RowSource = MyRecordSource
If Me![lstContList].ListCount = 0 Then
MsgBox " There are no containers with this criteria. Sorry ", 48
Me!cmdClear.SetFocus
Else
Me![lstContList].SetFocus
End If

And this is the function which retrieves the values and allows simultaneous searches (the same principle as in the link provided by this website) :

If fieldvalue <> "" Then
 If argcount > 0 Then
     mycriteria = mycriteria & " and "
     
 End If
 
 Select Case fieldo
    Case "myd1"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd2"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd3"
       wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
       mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
       
    Case "myd4"
       wtf3 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
       mycriteria = (mycriteria & fieldname & " <= " & Chr(35) & wtf3 & Chr(35))
    
    Case "myd5"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
       Case Else
 
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & Chr(39))
      End Select
  
  argcount = argcount + 1
 End If

I need to make a search option for a specific date range, not for a certain value. This I managed to do in the following code:

If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
        If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
        strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
                   "# And #" & Me![txtEndDate] & "# Order By [BirthDate];"

The question is whether it is possible to incorporate my date range search into my existing search procedure to provide and option of searching multiple fields simultaneously.

E.g. Currently I can search for a date and status, or a date and container size, but i can only search for a date range, without any other criteria.

I really hope I've explained everything clearly, if not I really apologize because english is not my first language. In this case please ask questions and I will try to explain better.

Thanks for your attention
Best regards, Eugene.

 

[Remou]

Great!

 

[Eugenios]

thanks thanks thanks again