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.
<script language=vbscript event=onclick for=cmdButton>
'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 '%'"
srchA = "[A] like '%" & strA & "%'"
If strB = "" Then
srchB = "[B] like '%'"
srchB = "[B] like '%" & strB & "%'"
If strC = "" Then
srchA = "[C] like '%'"
srchC = "[C] like '%" & strC & "%'"
myFilter = srchA & strAND & srchB & strAND & srchC
If myFilter = "AND AND AND" Then
msgbox("please enter something to filter for")
msodsc.recordsetdefs.item(0).serverfilter = myFilter