Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search multiple fields simultaneously with data ranges 1

Status
Not open for further replies.
Eugenios

Eugenios

Technical User
Jul 10, 2008
32
LV
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 :
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:
Code: 
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) :
Code: 
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:

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.
 
Sort by date Sort by votes
It should be possible to simply add this:

"AND [BirthDate] Between #" & Me![txtBeginDate] & _
"# And #" & Me![txtEndDate]

To your existing criteria. Roughly:

Code: 
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"

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

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
 
Upvote 0 Downvote


I would use this formatting yyyy-mm-dd for the date and point that Between ... And ... would work even when end point is grater than the start point, thus there is no need for the extra IF block
 
Upvote 0 Downvote
Right, I've tried to do it this way, but I cannot seem to retrieve the data to the list box. Do I need to make changes to the function that builds the SQL statement? (the 2nd piece of code I've presented)
 
Upvote 0 Downvote
What is the SQL output from the debug.print?
 
Upvote 0 Downvote
If I search just for a period, i get this in the immediate pane:

AND [Depot In Date] Between #01/05/2008# And #30/07/2008#

And the listbox is empty
 
Upvote 0 Downvote
Ok. may a small change:

MySQL = "SELECT * FROM qryContLife WHERE 1=1 "

This means that you will always have a where statement.
 
Upvote 0 Downvote
gonna try this tmr, thx, will keep posted
 
Upvote 0 Downvote
gonna try this tmr, thx, will keep posted
 
Upvote 0 Downvote
Code: 
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"

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

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
 
Upvote 0 Downvote
The code you posted does not include my suggestion which was to use this line:

MySQL = "SELECT * FROM qryContLife WHERE 1=1 "

In place of the existing line. Note 1=1 at the end of the line.
 
Upvote 0 Downvote
Sorry my friend, but in anycase it doesn't work with your suggestion.
 
Upvote 0 Downvote
In what way does it not work? Is there an error? What is the SQL returned?
 
Upvote 0 Downvote
Nope, nothing happens, the whole search stops working, the list box is blank when i search for smth. For example i tried the container size, the output of the immediate thing is

[Container Size] Like '4SD*'
[Container Size] Like '4SD*'
[Container Size] Like '4SD*'
[Container Size] Like '4SD*'
[Container Size] Like '4SD*'

I clicked it 5 times... the date doesn't work too of course.

There are no errors.
 
Upvote 0 Downvote
Please post the full SQL, not just the criteria:

Debug.print MySQL & mycriteria
MyRecordSource = MySQL & mycriteria
 
Upvote 0 Downvote
erm, sorry can u please explain what u r asking for ? because im not very sure ;)
 
Upvote 0 Downvote
If you look at your code, there is a line:

MyRecordSource = MySQL & mycriteria

I want to know what the contents of MySQL and mycriteria are, so I suggested adding a debug.print before this line to return this information to the immediate window. You can then cut and paste the line here.
 
Upvote 0 Downvote
Oh ok ;) this is the output:

SELECT * FROM qryContLife WHERE AND [Depot In Date] Between #05/01/2008# And #30/07/2008# AND [Depot In Date] Between #2008/01/05# And #2008/07/30#
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
2K
SoftwareRT
SoftwareRT
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
349
Dan Stoica
Dan Stoica
Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
420
Moss100
Moss100
JaineyBlue
  • Locked
  • Question
DLookup with criteria 1
Replies
2
Views
299
JaineyBlue
JaineyBlue
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
348
TheAceMan1
TheAceMan1

Part and Inventory Search

Sponsor

Back
Top