Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Searching by one or more fields

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,49338
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Searching by one or more fields
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Searching by one or more fields - 7/11/2007 5:12:47 AM   
  zeg37

 

Posts: 15
Score: 0
Joined: 6/16/2006
Status: offline 		Hi,

I have a MS Access data access page that I'm trying to set up as a search page.  I have three fields (ClientName, JobType, DateCreated) by which I want to potentially filter my records.  I would like to be able to search by either 1 field or any combination of 2 or more.  My code works successfully for searching by each field singularly or when using two of the fields together but I can't get it to use all three at one time.  Below is my code:

<SCRIPT language=vbscript>
Dim fInited
fInited=FALSE
 
Sub OnFilterComboChange()
Dim stWhere
 
If (vClientName.value = ""and vJobType.value = "" and vCreateDate.value = "") Then
            stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
            stWhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
            stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & "" 
Elseif (vClientName.value <> "") and (vJobtype.value <> "")Then
            stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
            stwhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
ElseIf (vClientName.value <> "") and (vCreateDate.value <> "") Then
            stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
            stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & ""
ElseIf (vJobType.value <> "") and (vCreateDate.value <> "")Then
            stWhere = "Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
            stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & ""
ElseIf (vClientName.value <> "") Then
            stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
ElseIf (vJobType.value <> "")Then
            stWhere = "Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
End if
MSODSC.RecordsetDefs.Item("ProjectClientFile Query").ServerFilter = stWhere
End Sub
</SCRIPT>

Oddly, the following code will allow me to search by all three and I based my initial 4 lines of code above on it:

<SCRIPT language=vbscript>
Dim fInited
fInited=FALSE
 
Sub OnFilterComboChange()
Dim stWhere
 
If (vClientName.value = "")and(vJobType.value = "")and (vCreateDate.value = "")Then
            stWhere = "Client.ClientName Like'" & "%" & "?????????" & "%" & "'"
Else
            stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
            stWhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
            stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & "" 
End if
MSODSC.RecordsetDefs.Item("ProjectClientFile Query").ServerFilter = stWhere
 
End Sub
</SCRIPT>


I have tried several variations on the code without success.  Microsoft's library has one white paper on custom filtering however it's example only uses two fields and requires that both are used.  Any ideas would be very much appreciated.

Thank you,
Zeg37

Dim fInited
fInited=FALSE

Sub OnFilterComboChange()
Dim stWhere

If (vClientName.value = ""and vJobType.value = "" and vCreateDate.value = "") Then
stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
stWhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & ""
Elseif (vClientName.value "") and (vJobtype.value "")Then
stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
stwhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
ElseIf (vClientName.value "") and (vCreateDate.value "") Then
stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & ""
ElseIf (vJobType.value "") and (vCreateDate.value "")Then
stWhere = "Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & ""
ElseIf (vClientName.value "") Then
stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
ElseIf (vJobType.value "")Then
stWhere = "Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
End if
MSODSC.RecordsetDefs.Item("ProjectClientFile Query").ServerFilter = stWhere
End Sub
 
 
Post #: 1
 
RE: Searching by one or more fields - 7/11/2007 6:53:26 AM   
  ehvbs

 

Posts: 2200
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi zeg37,

while it's not impossible to get it 'right' by using If/ElseIf and 'from scratch' concatenation,
I don't think that the time spend on planning, implementing, and debugging such code is
used well. This code:


      

and its output:


      

may look complicated, but it proves a very simple concatenation strategy

     ' build Filter for current test case
     Dim sWhere : sWhere = ""
     If "" <> sClientName   Then Add Expression to sWhere
     If "" <> sJobType       Then Add Expression to sWhere
     ...
     If "" <> sVariable112  Then Add Expression to sWhere
     sWhere = Trim( Mid( sWhere, Len( "AND " ) + 1 ) )

and the dirty trick of zapping the first "AND "

  (1) will handle all possible test cases

  (2) makes it easy to add a few more variables

  (3) allows custom code for different data types

Good luck!

ehvbs

(in reply to zeg37)
 
 
Post #: 2
 
RE: Searching by one or more fields - 7/11/2007 7:09:44 AM  1 votes
  Country73


Posts: 733
Score: 10
Joined: 8/25/2004
From: USA
Status: offline 		ehvbs beet me to it, but here's a different route.


' Value of 0 (Zero) is reserved as NO entry for that value
ClientName = 0
JobType = 0
CreateDate = 0

' Value of 1 is reserved for entry's made for the value
IF vClientName.Value <> "" THEN ClientName = 1
IF vJobType.Value <> "" THEN JobType = 1
IF vCreateDate.Value <> "" THEN CreateDate = 1

SearchValue = ClientName & "," & JobType & "," & CreateDate

   SELECT CASE SearchValue
       CASE "0,0,0"    'All items left blank
           'This is where you create your values to search on.
       'Example:
           'stWhere = "Client.ClientName Like'" & "%" & vClientName.value & "%" & "'"
           'stWhere = stWhere & "AND Project.JobType Like'" & "%" & vJobType.value & "%" & "'"
           'stWhere = stWhere & "AND Project.DateCreated>=" & "#" & vCreateDate.value & "#" & "" 
       CASE "1,0,0"    'Search on vClientName.Value only
      
       CASE "1,1,0"    'Search on vClientName.Value AND vJobType.Value
      
       CASE "1,1,1"    'Search on vClientName.Value AND vJobType.Value AND vCreateDate.Value
      
       CASE "1,0,1"    'Search on vClientName.Value AND vCreateDate.Value
      
       CASE "0,1,1"    'Search on vJobType.Value AND vCreateDate.Value
      
       CASE "0,0,1"    'Search on vCreateDate.Value only
      
       CASE "0,1,0"    'Search on vJobType.Value only
      
   END SELECT
  
   MSODSC.RecordsetDefs.Item("ProjectClientFile Query").ServerFilter = stWhere

(in reply to zeg37)
 
 
Post #: 3
 
RE: Searching by one or more fields - 7/11/2007 7:33:23 AM   
  ehvbs

 

Posts: 2200
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi Country73,

checking the conditions first - perhaps like this

  IF "" = Trim( vClientName.Value ) THEN ClientName = 0  ELSE ClientName = 1

to avoid having to do it at/in different lines - and using Select Case
to handle the possible 8 combinations systematically is a big improvement and
doesn't force you to use the dirty trick of zapping the first "AND ". But I don't
like to have to write the concatenation code for each variable more than once.
Even if you use "copy, paste, edit" - there will come the time to add a further
variable or change the comparison operator for a field: then you'll have to
add a lot of Case "*,*,*,*" and/or edit many not exactly identical but very
similar expressions.



(in reply to Country73)
 
 
Post #: 4
 
RE: Searching by one or more fields - 7/11/2007 7:36:49 AM   
  ebgreen


Posts: 5035
Score: 31
Joined: 7/12/2005
Status: online 		It's true that the drawback to Select-Case is that it is binary. So every time that you add a new search field you double the number of cases. Right now with 3 fields you have 8 cases. With 4 fields you would have 16.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to ehvbs)
 
 
Post #: 5
 
RE: Searching by one or more fields - 7/11/2007 7:56:16 AM   
  ehvbs

 

Posts: 2200
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		If you add a simple sub to add an element to an array:

Sub addToArray( aX, vElm )
   ReDim Preserve aX( UBound( aX ) + 1 )
  aX( UBound( aX ) ) = vElm
End Sub

to my first script, you can get rid of the trick and simplify the 'handle the variables'
code to:

     ' build Filter for current test case
     Dim aWheres : aWheres = Array()
     If "" <> sClientName   Then
        sClientName = Replace( sClientName, "'", "''" )
        addToArray aWheres, sClientName
     End If
     If "" <> sJobType      Then
        addToArray aWheres, "JobType = " + sJobType
     End If
     If "" <> sDateCreated  Then
        Dim dtDateCreated : dtDateCreated = CDate( sDateCreated )
        addToArray aWheres,   "DateCreated = #" _
                             & Month( dtDateCreated ) & "/" _
                             & Day(   dtDateCreated ) & "/" _
                             & Year(  dtDateCreated ) & "# "
     End If
     WScript.Echo "Filter", """" + Join( aWheres, " AND " ) + """"

(in reply to ebgreen)
 
 
Post #: 6
 
RE: Searching by one or more fields - 7/11/2007 11:54:24 PM   
  Country73


Posts: 733
Score: 10
Joined: 8/25/2004
From: USA
Status: offline 		Thanks for that bit of insight, ebgreen and ehvbs.

I have a couple of scripts put together, just like the Select Case I posted, but I know for a fact that the database they connect to will not change.

One of the scripts I have this setup for will search a database that only lists: MachineType, RAM_Total, Deployment_Date (those are the only fields in the database). This database has been setup like this for over 3 years and if any modifications need to come about, then a whole new database will need to be created.
So for "THIS" instance it would work fine.
If your database has a lot more fields that could eventually be used to search against, then the Select Case method may not be the right choice. It really depends on the database you are setting this up for, and how much work you want to put into it.

Just trying to throw some ideas out there so that there's a few choices as to how to handle different scenarios.


P.S.
I really like the way you threw this together:
quote:

IF "" = Trim( vClientName.Value ) THEN ClientName = 0  ELSE ClientName = 1

I didn't even think about it that way. (Used to really drawing everything out pretty lengthy when creating my scripts so that others in my area can follow it pretty well without asking me too many questions about it)

< Message edited by Country73 -- 7/11/2007 11:58:02 PM >

(in reply to ehvbs)
 
 
Post #: 7
 
RE: Searching by one or more fields - 7/12/2007 11:52:43 PM   
  zeg37

 

Posts: 15
Score: 0
Joined: 6/16/2006
Status: offline 		Thank you all for your suggestions.  I was out of the office yesterday, so I will be trying them out today.  (I'm pretty green when it comes to writing code so I may be back for help.)

Again...thank you very much.  I've been struggling with this for quite awhile.

Zeg

(in reply to Country73)
 
 
Post #: 8
 
RE: Searching by one or more fields - 7/13/2007 1:14:41 AM   
  ebgreen


Posts: 5035
Score: 31
Joined: 7/12/2005
Status: online 		I just want to be explicit that I don't think that there is anything wrong with the Select-Case method. You just need to be aware of it's limitations.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to zeg37)
 
 
Post #: 9
 
RE: Searching by one or more fields - 7/16/2007 1:15:27 AM   
  zeg37

 

Posts: 15
Score: 0
Joined: 6/16/2006
Status: offline 		Hi,

I wanted to add an update on my progress.  I used the select-case method and with just a little tweaking was able to write code that successfully works for any variation of my three varibles.  YEAH!

Thank you all very much for your help. 

Zeg

(in reply to ebgreen)
 
 
Post #: 10
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Searching by one or more fields Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI