469,607 Members | 1,953 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,607 developers. It's quick & easy.

Search multiple fields simultaneously for values within a certain range

13
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowCont_Click()
  2. Dim MySQL As String, mycriteria As String, MyRecordSource As String
  3. Dim argcount As Integer
  4. Dim Tmp As Variant
  5. mycriteria = " "
  6. argcount = 0
  7. MySQL = "SELECT * FROM qryContLife  WHERE "
  8.  
  9. Addwtf [textbox], "[Arrival Date]", mycriteria, argcount, "textbox"
  10.  
  11. Debug.Print mycriteria
  12. If mycriteria = " " Then
  13. mycriteria = "True"
  14. End If
  15.  
  16. MyRecordSource = MySQL & mycriteria
  17. Me![listbox].RowSource = MyRecordSource
  18. If Me![listbox].ListCount = 0 Then
  19. MsgBox " There are no containers with this criteria. Sorry ", 48
  20. Me!cmdClear.SetFocus
  21. Else
  22. Me![listbox].SetFocus
  23. End If
  24.  
  25. End Sub
  26.  
This is the code for the sub which builds the SQL statement and through which the values are returned to the listbox:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Addwtf(fieldvalue As Variant, fieldname As String, mycriteria As String, argcount As Integer, fieldo As String)
  2. Dim wtf1 As String, wtf2, wtf3 As String
  3.  
  4.  If fieldvalue <> "" Then
  5.  If argcount > 0 Then
  6.      mycriteria = mycriteria & " and "
  7.  
  8.  End If
  9.  
  10.  Select Case fieldo
  11.  
  12. Case "textbox"
  13.        wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
  14.        mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
  15.  
  16.   argcount = argcount + 1
  17.  End If
  18. End Sub
  19.  
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.
Jul 8 '08 #1
10 3697
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim MyRS As DAO.Recordset
  3. Dim strSQL As String
  4.  
  5. Set MyDB = CurrentDb()
  6.  
  7. If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
  8.   If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
  9.     strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
  10.              "# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
  11.     Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  12.       If Not MyRS.BOF And Not MyRS.EOF Then     'valid Records
  13.         Do While Not MyRS.EOF
  14.           Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate]  'Test Loop
  15.          MyRS.MoveNext
  16.         Loop
  17.       Else
  18.         MsgBox "No Records meet your criteria", vbExclamation, "No Records"
  19.       End If
  20.   Else
  21.     MsgBox "End Date must be > Begin Date", vbExclamation, "Invalid Date"
  22.       Exit Sub
  23.   End If
  24. Else
  25.   'simply fall through, don't process
  26.   Exit Sub
  27. End If
  28.  
  29. MyRS.Close
  30. Set MyRS = Nothing
P.S. - Any questions, feel free to ask.
Jul 8 '08 #2
Eugenio
13
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 ;)
Jul 8 '08 #3
ADezii
8,800 Expert 8TB
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.
Jul 8 '08 #4
Eugenio
13
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  3.  
  4.  If Not MyRS.BOF And Not MyRS.EOF Then     'valid Records
  5.           Do While Not MyRS.EOF
  6.           Debug.Print MyRS![LastName] & " ==> " & MyRS![BirthDate]  'Test Loop
  7.           MyRS.MoveNext
  8.           Loop
  9.  
  10. Else
  11.  
  12. MsgBox "No Records meet your criteria", vbExclamation, "No Records"
  13.  
  14. End If
  15.  
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 ?
Jul 9 '08 #5
NeoPa
32,200 Expert Mod 16PB
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.
Jul 9 '08 #6
ADezii
8,800 Expert 8TB
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.
Jul 9 '08 #7
Eugenio
13
thanks guys, i will be working on this and will post if I encounter any difficulties. Sorry for the confusion I caused. Thank you.
Jul 9 '08 #8
Eugenio
13
right, i've managed to create a search for a sepcific date range using your example and it looks like this:
Expand|Select|Wrap|Line Numbers
  1. Dim MyRecordSource As String
  2.       Dim strSQL As String
  3.  
  4.  
  5.         If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
  6.         If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
  7.         strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
  8.                    "# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
  9.  
  10.  
  11.  
  12.         End If
  13.         End If
  14.  
  15.         MyRecordSource = strSQL
  16.         Me![lstContlist].RowSource = MyRecordSource
  17.         If Me![lstContlist].ListCount = 0 Then
  18.         MsgBox " There are no containers with this criteria. Sorry ", 48
  19.         Me!cmdClear.SetFocus
  20.         Else
  21.         Me![lstContlist].SetFocus
  22.         End If
  23.  
It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function:
Expand|Select|Wrap|Line Numbers
  1. Dim wtf1 As String, wtf2, wtf3 As String
  2.  
  3.  If fieldvalue <> "" Then
  4.  If argcount > 0 Then
  5.      mycriteria = mycriteria & " and "
  6.  
  7.  End If
  8.  
  9.  Select Case fieldo
  10.     Case "myd1"
  11.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  12.  
  13.     Case "myd2"
  14.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  15.  
  16.     Case "myd3"
  17.        wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
  18.        mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
  19.  
  20.     Case "myd4"
  21.        wtf3 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
  22.        mycriteria = (mycriteria & fieldname & " <= " & Chr(35) & wtf3 & Chr(35))
  23.  
  24.     Case "myd5"
  25.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  26.  
  27.        Case Else
  28.  
  29.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & Chr(39))
  30.       End Select
  31.  
  32.   argcount = argcount + 1
  33.  End If
  34.  
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.
Jul 9 '08 #9
ADezii
8,800 Expert 8TB
right, i've managed to create a search for a sepcific date range using your example and it looks like this:
Expand|Select|Wrap|Line Numbers
  1. Dim MyRecordSource As String
  2.       Dim strSQL As String
  3.  
  4.  
  5.         If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
  6.         If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
  7.         strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] & _
  8.                    "# And #" & Me![txtEndDate] & "# Order By [BirthDate];"
  9.  
  10.  
  11.  
  12.         End If
  13.         End If
  14.  
  15.         MyRecordSource = strSQL
  16.         Me![lstContlist].RowSource = MyRecordSource
  17.         If Me![lstContlist].ListCount = 0 Then
  18.         MsgBox " There are no containers with this criteria. Sorry ", 48
  19.         Me!cmdClear.SetFocus
  20.         Else
  21.         Me![lstContlist].SetFocus
  22.         End If
  23.  
It shows the found values in the listbox, but is it possible to incorporate it into the existing multiple search function:
Expand|Select|Wrap|Line Numbers
  1. Dim wtf1 As String, wtf2, wtf3 As String
  2.  
  3.  If fieldvalue <> "" Then
  4.  If argcount > 0 Then
  5.      mycriteria = mycriteria & " and "
  6.  
  7.  End If
  8.  
  9.  Select Case fieldo
  10.     Case "myd1"
  11.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  12.  
  13.     Case "myd2"
  14.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  15.  
  16.     Case "myd3"
  17.        wtf2 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
  18.        mycriteria = (mycriteria & fieldname & " = " & Chr(35) & wtf2 & Chr(35))
  19.  
  20.     Case "myd4"
  21.        wtf3 = Mid$(fieldvalue, 4, 2) & "/" & Mid$(fieldvalue, 1, 2) & "/" & Mid$(fieldvalue, 7, 4)
  22.        mycriteria = (mycriteria & fieldname & " <= " & Chr(35) & wtf3 & Chr(35))
  23.  
  24.     Case "myd5"
  25.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
  26.  
  27.        Case Else
  28.  
  29.        mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & Chr(39))
  30.       End Select
  31.  
  32.   argcount = argcount + 1
  33.  End If
  34.  
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:
    Expand|Select|Wrap|Line Numbers
    1. strSQL = "Select * From tblTest Where [BirthDate] Between #" & Me![txtBeginDate] &  "# And #" & Me![txtEndDate] & "# "
  2. then build on the strSQL, as in:
    Expand|Select|Wrap|Line Numbers
    1. strSQL = strSQL & "AND <specify other criteria here> AND <specify other criteria here> "
  3. Tack on a final Order By Clause, if needed:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Jul 9 '08 #10
Eugenio
13
Expand|Select|Wrap|Line Numbers
  1. If IsDate(Me![txtBeginDate]) And IsDate(Me![txtEndDate]) Then
  2.    If CDate(Me![txtBeginDate]) < CDate(Me![txtEndDate]) Then
  3.       mycriteria = mycriteria _
  4.         & " AND [BirthDate] Between #" _
  5.         & Format(Me![txtBeginDate], "yyyy/mm/dd") _
  6.         & "# And #" _
  7.         & Format(Me![txtEndDate], "yyyy/mm/dd") & "# "
  8.    End If
  9. End If
  10.  
  11.  
Like this ? I cannot manage to retrieve the data to the listbox in this way though.
Jul 11 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

32 posts views Thread by tshad | last post: by
14 posts views Thread by Thurston Manson | last post: by
8 posts views Thread by Miro | last post: by
reply views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.