Search multiple fields simultaneously with data ranges 1

Eugenios · Jul 10, 2008

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:

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.

Remou · Jul 16, 2008

Please add 1=1 to the first SQL line as was previously mentioned in my posts 11 Jul 08 5:42 and 15 Jul 08 15:59.

MySQL = "SELECT * FROM qryContLife WHERE [red]1=1[/red]

Eugenios · Jul 16, 2008

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #01/05/2008# And #30/07/2008# AND [Depot In Date] Between #2008/05/01# And #2008/07/30#

Remou · Jul 16, 2008

Perhaps you could add a few words to that last post?

Eugenios · Jul 16, 2008

Right, what I experiece when I search for a date range and other values.

When I search for a date range, the output is this :

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #05/01/2008# And #01/07/2008# AND [Depot In Date] Between #2008/01/05# And #2008/07/01#

And the listbox shows the requested values, in a weird way, but still shows them (weird because the first input date requires a mm/dd/yyyy format and the second dd/mm/yyyy)

Here's where it gets really strange: If I search for a date and a status, the output is this:

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #05/01/2008# And #01/07/2008# and [Status] Like 'RM*' AND [Depot In Date] Between #2008/01/05# And #2008/07/01#

And the list box shows the values which fall under these dates and this status

But, if I search for the date range and any other criteria except the status, i still get the correct output (OwnerCode and date):

SELECT * FROM qryContLife WHERE 1=1 [OwnerCode] Like 'AHLERS*' and AND [Depot In Date] Between #05/01/2008# And #01/07/2008# AND [Depot In Date] Between #2008/01/05# And #2008/07/01#

But the listbox is blank.

Remou · Jul 16, 2008

This is beginning to look like a bit iof a mess. Please post the entire code as it now stands with some explaination as to why there are so many dates involved:

Addwtf [myd3], [red]"[Arrival Date]"[/red], mycriteria, argcount, "myd3"
Addwtf [myd4], [red]"[Depot In Date]"[/red], mycriteria, argcount, "myd4"
& " AND [red][BirthDate][/red] Between #"

Also, how many criteria have you got? Is there any formatting on the controls that would oblige you to enter dates in a variety of formats?

Please answer all questions.

Eugenios · Jul 16, 2008

Code:

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 1=1"

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"
Addwtf [myd8], "[Status]", mycriteria, argcount, "myd8"

If IsDate(Me![myd6]) And IsDate(Me![myd7]) Then
   If CDate(Me![myd6]) < CDate(Me![myd7]) Then
      mycriteria = mycriteria _
        & " AND [Depot In Date] Between #" _
        & Format(Me![myd6], "yyyy/mm/dd") _
        & "# And #" _
        & Format(Me![myd7], "yyyy/mm/dd") & "# "
   End If
End If

Debug.Print MySQL & 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


End Sub

There are 7 criterias altogether i guess.

Code:

Addwtf [myd3], "[Arrival Date]", mycriteria, argcount, "myd3"
Addwtf [myd4], "[Depot In Date]", mycriteria, argcount, "myd4"
Addwtf [myd5], "[Container Size]", mycriteria, argcount, "myd5"
Addwtf [myd8], "[Status]", mycriteria, argcount, "myd8"

This is a search for an exact value. For specific containers which fall under these criteria.

Code:

If IsDate(Me![myd6]) And IsDate(Me![myd7]) Then
   If CDate(Me![myd6]) < CDate(Me![myd7]) Then
      mycriteria = mycriteria _
        & " AND [Depot In Date] Between #" _
        & Format(Me![myd6], "yyyy/mm/dd") _
        & "# And #" _
        & Format(Me![myd7], "yyyy/mm/dd") & "# "
   End If
End If

This part does the date range search, for containers which fall under a specified time period.

All date fields have a short date formatting.

Here is the code for the Addwtf sub:

Code:

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 "myd1"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd2"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd3"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd4"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
    
    Case "myd5"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
        
    Case "myd8"
        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
End Sub

I really hope i answered everything and big thanks for you attention and patience.

Remou · Jul 16, 2008

Change this:

If fieldvalue <> "" Then
If argcount > 0 Then
mycriteria = mycriteria & " and "

End If

To:

mycriteria = mycriteria & " and "

That is, get rid of the If statements.

You should not use Like with full dates, use equals instead.

Otherwise, no matter how many times I run this, I do not get the output you show.

Eugenios · Jul 16, 2008

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #2008/05/01# And #2008/07/01#

this is what i get if i just input the date range, and I get the values in the listbox, but as soon as I add something else to the search, Container Size or Status, the listbox is blank.

Remou · Jul 16, 2008

Is this including the change I just suggested? Also, please post the SQL that does not work.

Eugenios · Jul 16, 2008

The only thing that changes with your suggestion is that the function takes all the fields no matter whether they r empty or not, the output in this case looks like this:

SELECT * FROM qryContLife WHERE 1=1 and [Container Number] Like '*' and [OwnerCode] Like '*' and [Arrival Date] Equals '*' and [Depot In Date] Equals '*' and [Container Size] Like '*' and [Status] Like '*' AND [Depot In Date] Between #2008/05/01# And #2008/07/01#

It doesn't show the dates in the listbox. The output of the SQL that doesn't work looks perfectly fine, but just doesn't show in the listbox.

Remou · Jul 16, 2008

By Equals I mean equals sign "=".

Remou · Jul 16, 2008

I am rapidly coming to the conclusion that you have either two versions or more code. The code you posted with the changes I suggested does not return the line you show. I'll add that in no case has the code you posted returned the lines you show.

Eugenios · Jul 16, 2008

Code:

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 1=1"

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"
Addwtf [myd8], "[Status]", mycriteria, argcount, "myd8"

If IsDate(Me![myd6]) And IsDate(Me![myd7]) Then
   If CDate(Me![myd6]) < CDate(Me![myd7]) Then
      mycriteria = mycriteria _
        & " AND [Depot In Date] Between #" _
        & Format(Me![myd6], "dd/mm/yyyy") _
        & "# And #" _
        & Format(Me![myd7], "dd/mm/yyyy") & "# "
   End If
End If

Debug.Print MySQL & 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


End Sub

Private Sub Command120_Click()
      Dim MyRecordSource As String
      Dim strSQL As String

       If IsDate(Me![myd6]) And IsDate(Me![myd7]) Then
       If CDate(Me![myd6]) < CDate(Me![myd7]) Then
        strSQL = "Select * From qryContLife Where [Depot In Date] Between #" & Format(Me![myd6], "dd-mm-yyyy") & _
                   "# And #" & Format(Me![myd7], "dd-mm-yyyy") & "# Order By [Depot In Date];"
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
End Sub

This code as posted and the Addwtf sub as this:

Code:

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

     mycriteria = mycriteria & " and "
     
 
 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"
       mycriteria = (mycriteria & fieldname & " = " & Chr(39) & fieldvalue & "*" & Chr(39))
       
    Case "myd4"
       mycriteria = (mycriteria & fieldname & " = " & Chr(39) & fieldvalue & "*" & Chr(39))
    
    Case "myd5"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
        
    Case "myd8"
        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 Sub

This, when I fill in the date range fields and the status field for example, returns this value :

SELECT * FROM qryContLife WHERE 1=1 and [Container Number] Like '*' and [OwnerCode] Like '*' and [Arrival Date] = '*' and [Depot In Date] = '*' and [Container Size] Like '*' and [Status] Like 'RM*' AND [Depot In Date] Between #01/05/2008# And #01/07/2008#

Isn't this right? But it still doesn't show the values in the listbox.

Eugenios · Jul 16, 2008

Sorry, don't pay attention to the :

Private Sub Command120_Click()

I accidently copied that together with the required code

Remou · Jul 16, 2008

Ok, I was not clear enough, I intended that you got rid of one if statement, not both:

Code:

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
     mycriteria = mycriteria & " and "
     
 
 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"
    'Date field
       mycriteria = mycriteria & fieldname & " = #" & Format(fieldvalue,"yyyy/mm/dd") & "#"
       
    Case "myd4"
    'Date field
       mycriteria = mycriteria & fieldname & " = #" & Format(fieldvalue,"yyyy/mm/dd") & "#"
    
    Case "myd5"
       mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & "*" & Chr(39))
       
        
    Case "myd8"
        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
End Sub

Eugenios · Jul 16, 2008

Did what you've said, left 1 if statement at the beggining of the Addwtf sub, haven't changed anything in the button sub, this is whats the output like:

SELECT * FROM qryContLife WHERE 1=1 [Container Number] Like '*'[OwnerCode] Like '*'[Arrival Date] = '*'[Depot In Date] = '*'[Container Size] Like '*'[Status] Like '*' AND [Depot In Date] Between #05/01/2008# And #01/07/2008#

I'm getting really upset over this ;E

Remou · Jul 16, 2008

Where has the 'and' gone this time? What have you done to Addwtf to get rid of the 'and'?

mycriteria = mycriteria & " and "

Just cut and paste in the code I show above for Addwft and rename your existing addwft to brokenwft.

Eugenios · Jul 16, 2008

yay, we're on the right track, it works with simultaneous searches, this is the output:

SELECT * FROM qryContLife WHERE 1=1 and [Status] Like 'RM*' AND [Depot In Date] Between #05/01/2008# And #01/05/2008#

and it gets shown in the listbox, but the dates are weird.

I input from 05/01/2008 to 01/05/2008 and the fields I get are with dates like this:

09/04/2008
25/01/2008
03/04/2008
21/03/2008
04/04/2008
20/02/2008
21/01/2008

its driving me nuts man ;DD

Remou · Jul 16, 2008

Please put these date formats back the right way, they should be yyyy/mm/dd, like so:

Code:

If IsDate(Me![myd6]) And IsDate(Me![myd7]) Then
   If CDate(Me![myd6]) < CDate(Me![myd7]) Then
      mycriteria = mycriteria _
        & " AND [Depot In Date] Between #" _
        & Format(Me![myd6], "yyyy/mm/dd") _
        & "# And #" _
        & Format(Me![myd7], "yyyy/mm/dd") & "# "
   End If
End If

Eugenios · Jul 16, 2008

Remou, thanks alot, everything works now, you've done a great job. You're a very patient and helpful person. Thank you.

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!

Search multiple fields simultaneously with data ranges 1

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor