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.

[ADezii]

To search for Records falling within a specified Date Range, use the BETWEEN operator making sure to delimit the Date Fields with the '#' qualifier. Assuming entry Fields of [txtBeginDate] and [txtEndDate], the general logic would be:

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
 
Set MyDB = CurrentDb()
 
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];"
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
      If Not MyRS.BOF And Not MyRS.EOF Then     'valid Records
        Do While Not MyRS.EOF
          Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate]  'Test Loop
         MyRS.MoveNext
        Loop
      Else
        MsgBox "No Records meet your criteria", vbExclamation, "No Records"
      End If
  Else
    MsgBox "End Date must be > Begin Date", vbExclamation, "Invalid Date"
      Exit Sub
  End If
Else
  'simply fall through, don't process
  Exit Sub
End If
 
MyRS.Close
Set MyRS = Nothing

P.S. - Any questions, feel free to ask.

[Eugenio]

Thanks for your attention. Right, is it possible to incorporate the example u gave me into the existing code? I mean so that I would be able to make searches using various criteria e.g. sort the data by the date range between 01/01/2008 and 01/02/2008 and some other piece of data like container size for instance.

I really hope I'm being clear. Unfortunately I don't have a chance to test ur example right now, but I'm going to do that first thing in the morning and inform u of the results.

thank u again ;)

[ADezii]

Thanks for your attention. Right, is it possible to incorporate the example u gave me into the existing code? I mean so that I would be able to make searches using various criteria e.g. sort the data by the date range between 01/01/2008 and 01/02/2008 and some other piece of data like container size for instance.

I really hope I'm being clear. Unfortunately I don't have a chance to test ur example right now, but I'm going to do that first thing in the morning and inform u of the results.

thank u again ;)

Code has been tested, and seems to work quite well, given the existing parameters.

[Eugenio]

ADezii, I never used the Recordset function so I don't fully understand how the code works. In your example you have a table with birthdates and a form with 2 text boxes, this part I get, but this one scares me:

 
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
 If Not MyRS.BOF And Not MyRS.EOF Then     'valid Records
          Do While Not MyRS.EOF
          Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate]  'Test Loop
          MyRS.MoveNext
          Loop
 
Else
 
MsgBox "No Records meet your criteria", vbExclamation, "No Records"
 
End If
 

The 5th line gives and error in the debugger, and how exactly it shows the retrieved data? does it open a new window or it requires a list box etc? And also where does the [Lastname] come from ?

[NeoPa]

Debug.Print shows data in the Immediate Pane. Use Alt-F11 to switch to the debugger from your Access window, then Ctrl-G to open, and switch to, the Immediate Pane. Your original post includes similar code so one would have thought this was familiar to you.

MyRS![LastName] is a reference to a field (called LastName) from the recordset provided by the SQL in strSQL (See line #1).
If you have no such field then you need to adjust the code to match what you are working with.

[ADezii]

Sorry Eugenio, but based on your original code, I made the false assumption that that you would fully comprehend the logic which I have posted. I completely apologize for this oversight. NeoPa provided the necessary explanations of my code in Post #6, and as far as a Recordset goes, in your case, it is a virtual representation of the SQL Statement represented by strSQL.

P.S. - Thanks NeoPa.

[Eugenio]

thanks guys, i will be working on this and will post if I encounter any difficulties. Sorry for the confusion I caused. Thank you.

[Eugenio]

right, i've managed to create a search for a sepcific date range using your example and it looks like this:

Dim MyRecordSource As String
      Dim strSQL As String
 
 
        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];"
 
 
 
        End If
        End If
 
        MyRecordSource = strSQL
        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
 

It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function:

Dim wtf1 As String, wtf2, wtf3 As String
 
 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
 

and allow it to search for records with the date range given and a specific status for example. in other words allow multiple search criteria?

P.s. I'm sorry in advance because this may sound stupid, but I'm using access for less than a year and still have a lot to learn.

[ADezii]

right, i've managed to create a search for a sepcific date range using your example and it looks like this:

Dim MyRecordSource As String
      Dim strSQL As String
 
 
        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];"
 
 
 
        End If
        End If
 
        MyRecordSource = strSQL
        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
 

It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function:

Dim wtf1 As String, wtf2, wtf3 As String
 
 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
 

and allow it to search for records with the date range given and a specific status for example. in other words allow multiple search criteria?

P.s. I'm sorry in advance because this may sound stupid, but I'm using access for less than a year and still have a lot to learn.

Here is where it would really become complicated.

1. You could use the following as your base Criteria String:
   

   strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] &  "# And #" & Me![txtEndDate] & "# "

2. then build on the strSQL, as in:
   

   strSQL = strSQL & "AND <specify other criteria here> AND <specify other criteria here> "

3. Tack on a final Order By Clause, if needed:
   

   strSQL = strSQL & "Order By [BirthDate];"

4. There are other Experts/Moderators who are much better than I in the SQL lingo, hopefully they will join the discussion, and provide you with a better response.

[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.