asked on

Multi-Criteria Filter Problem

Let's say I have a table with 3 fields(text) named A, B, and C. In some records A may be left blank while in other cases B and C will also have no values. It is not required that each field in a record be filled in with data. So now I have a table with a bunch of holes in it. Next I make a data access page to filter(search) out records based on the 3 criteria for A, B, and C. So now I have txtA, txtB, and txtC for my criteria plus a button to start the function. The code below shows how I'm doing it now. Using the [A] like '%' is handy with the ServerFilter because it almost doesn't exclude any records for the next criteria. However, using [A] like '%' will not return a record with the A field being Null. So I could filter for [A] like '%' AND [B] like '%JOHN%' AND [C] like '%DOE%' and I would see all the john doe records except for those with a Null value in the A field.
Maybe some sort of instr(myFilter) code is needed to get only the desired ServerFilter parameters into myFilter. I'm not really sure how though.

Thanks,
James

<script language=vbscript event=onclick for=cmdButton>
setFilter()
</script>
<script language=vbscript>
Function setFilter()
'str* is the actual textbox data, srch* is what it should be for ServerFilter speak
Dim strA, strB, strC, srchA, srchB, srchC, myFilter, strAND
strAND = " AND " 'this joins the criteria for the filter
'First get search criteria into a variable.
strA = txtA.innertext
strB = txtB.innertext
strC = txtC.innertext
'Next is where I am going down the wrong path I believe
If strA = "" Then
srchA = "[A] like '%'"
Else
srchA = "[A] like '%" & strA & "%'"
End If
If strB = "" Then
srchB = "[B] like '%'"
Else
srchB = "[B] like '%" & strB & "%'"
End If
If strC = "" Then
srchA = "[C] like '%'"
Else
srchC = "[C] like '%" & strC & "%'"
End If
myFilter = srchA & strAND & srchB & strAND & srchC
If myFilter = "AND AND AND" Then
msgbox("please enter something to filter for")
Else
msodsc.recordsetdefs.item(0).serverfilter = myFilter
End If
End Function
</script>

Open in new window

Scott McDaniel (EE MVE )

It's not clear what you're wanting to achieve ... do you want to show records where A IS NULL and B IS NOT NULL etc etc ...

nukewarm

ASKER

Yes. I cannot find an alternative to using lines 14-28. This filter function must match any one or all of the criteria. By using the Like operator to match any record in the table (lines 15, 20, 25) I allow the ServerFilter to return everything, except for null values. Say I hit the button without giving any criteria to search for, the ServerFilter would spit out the entire record set except for the those records which contain a null value in one of their fields.

I know, in theory, a way to do it. I'm not that good with VB however.
I need myFilter to only get the serverFilter statements that are needed by the user. This way I wouldn't have to worry about using the like '%' statement.

<script language=vbscript>
Function setFilter()
'str* is the actual textbox data, srch* is what it should be for ServerFilter speak
Dim strA, strB, strC, myFilter, strAND
strAND = " AND " 'this joins the criteria for the filter
'First get search criteria into a variable.
myFilter = ""
strA = txtA.innertext
strB = txtB.innertext
strC = txtC.innertext
'Now here is what I propose with out any kind of regard for syntax in vbscript
If strA = "" Then
Do Nothing
Else
write this string "[A] like '%" & strA & "%'" to myFilter
End If
If strB = "" Then
Do Nothing
Else
 If strLen(myFilter) > 0 Then 'this means there is already a serverFilter statement in myFilter so I AND in the next statement
 Append this string " AND [B] like '%" & strB & "%'" to myFilter
 Else ' myFilter is empty so I can add a normal serverFilter statement
 write this string "[B] like '%" & strB & "%'" to myFilter
 End If
End If
'and so on
If strC = "" Then
Do Nothing
Else
 If strLen(myFilter) > 0 Then
 Append this string " AND [C] like '%" & strB & "%'" to myFilter
 Else
 write this string "[C] like '%" & strB & "%'" to myFilter
 End If
End If

Open in new window

nukewarm

ASKER

Man I'm bad about this. It seems I must waste forum space to understand my questions. LOL. I've done this soo many times. Thanks for your interests in my problem though.
Here is the fix. This uses the real world field names though.

<SCRIPT language=vbscript event=onload for=window>
<!--
clearFilter()
-->
</SCRIPT>
 
<SCRIPT language=vbscript event=onclick for=cmdSearch>
setFilter()
</SCRIPT>
 
<SCRIPT language=vbscript>
Function clearFilter()
Dim myFilter
myFilter = "[id] < 0"
msodsc.RecordsetDefs.Item(0).ServerFilter = myFilter 
End Function
</SCRIPT>
 
<SCRIPT language=vbscript>
Function setFilter()
Dim strInv, strWo, strCustomer, strJob, myFilter, srchInv, srchWo, srchCustomer, srchJob, strAnd
strAnd = " AND "
strInv = txtInv.innerText
strWo = txtWO.innerText
strCustomer = txtCustomer.innerText
strJob = txtJob.innerText
myFilter = "" 'Make sure myFilter is empty
If len(strInv) = 0 Then
	If len(myFilter) > 0 Then
	myFilter = myFilter
	End If
Else
myFilter = myFilter & "[invoice] like '%" & strInv & "%'" 'no AND preceding this because it is first in line.
End If
If len(strWo) = 0 Then
	If len(myFilter) > 0 Then
	myFilter = myFilter
	Else
	myFilter = ""
	End If
Else
	If len(myFilter) > 0 Then
	myFilter = myFilter & " AND [work_order] like '%" & strWo & "%'" 'AND precedes statement because myFilter is not empty.
	Else
	myFilter = "[work_order] like '%" & strWo & "%'"
	End If
End If
 
If len(strCustomer) = 0 Then
	If len(myFilter) > 0 Then
	myFilter = myFilter
	Else
	myFilter = ""
	End If
Else
	If len(myFilter) > 0 Then
	myFilter = myFilter & " AND [customer_name] like '%" & strCustomer & "%'"
	Else 
	myFilter = "[customer_name] like '%" & strCustomer & "%'"
	End If
End If
 
If len(strJob) = 0 Then
	If len(myFilter) > 0 Then
	myFilter = myFilter
	Else myFilter = ""
	End If
Else
	If len(myFilter) > 0 Then
	myFilter = myFilter & " AND [job_name] like '%" & strJob & "%'"
	Else
	myFilter = "[job_name] like '%" & strJob & "%'"
	End If
End If
 
 
txtTest.innerText = myFilter
msodsc.RecordsetDefs.Item(0).ServerFilter = myFilter
End Function

Open in new window

Scott McDaniel (EE MVE )

Sometimes that's all it takes - writing them out so that others can understand them sometimes forces you to think through the process in a more complete - or different - manner. Glad we could be your "sounding board"!

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial