Match a Wildcard with the Number Data Type?

[kcddoorman]

I'm trying to make a multi criteria server filter and everything works fine when filtering for strings. When I add a number field to the filterable set I run into problems. Here is the VBScript I'm using.

<SCRIPT language=vbscript>
Function SetServerFilter()
dim myFilter, strCustomer, strJobName, strSalesPerson, strWorkOrder
 
If txtCustomer.value = "" Then
strCustomer = "[Customer Name] like '%'" 
Else
strCustomer = "[Customer Name] = '" & txtCustomer.value & "'"
End If
If txtJobName.value = "" Then
strJobName = "[Job Name] like '%'"
Else
strJobName = "[Job Name] like '%" & txtJobName.value & "%'"    
End If  
If txtSalesPerson.value = "" Then
strSalesPerson = "[SalesPerson] like '%'"
Else
strSalesPerson = "[SalesPerson] = '" & txtSalesPerson.value & "'"
End If
If txtWorkOrder = "" Then
strWorkOrder = "[WO NUMBER] = "   'I tried to leave it blank but this doesn't work.
Else
strWorkOrder = "[WO NUMBER] = " & txtWorkOrder.value
End If
 
      myFilter = strCustomer & "AND"& strSalesPerson & "AND" & strJobName & "AND" & strWorkOrder
 
      txtMyfilter.value = (myFilter)
   MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter
'test that doesn't work
   'MSODSC.RecordsetDefs.Item(0).ServerFilter = "[WO NUMBER] = "
End Function
 
 
</SCRIPT>
 

The first three If/Than/Else statements get the appropriate information into the serverfilter via myFilter. However, on the fourth IF/Than/Else statement I don't know how to get a wildcard to match with a number type value.
This string "[Number] = 32218" will work fine but I need something like this "[Number] = any number at all" to get the thing to work. It's either that or I can go back to the table and change the data type for [WO NUMBER] to text.

Thanks for any help you can give me.
James

[kcddoorman]

Well, Duh. I like IF/Than/Else statements so much here I simply add this to the code and it works just fine.

If txtWorkOrder.value = "" Then
      myFilter = strCustomer & "AND"& strSalesPerson & "AND" & strJobName '& "AND" & strWorkOrder
Else
myFilter = strCustomer & "AND"& strSalesPerson & "AND" & strJobName & "AND" & strWorkOrder
End If
 

[nico5038]

I never code filterings this "extensive", but give my users a description how to use the right-click popup form:
http://www.geocities.com/nico5038/xR...nstruction.zip

Success !

Nic;o)

[kcddoorman]

The built in filter and sort functions are great for forms. I use them everyday when looking for orders, but this function is done in a data access page. There is a record navigation control (in DAPs) that can do some of the things that the filter and sort can do (in forms). This page will be used by many people in our company, and some of them are not very computer literate like you and me. This page kind of resembles a search page they may have used on the internet so they feel more comfortable. Also, I don't have to teach them how to use the navigation button; I actually removed it all together from the page.

[nico5038]

Hmm, DAP's is a different story indeed. Must warn you that A2007 won't support DAP anymore....

For the number within number test you could try the CStr() function on the number to get a characterstring instead of a number. This combined with Instr() would give you success when the number(s) are found in the number.
Something like:

select * from tblx WHERE (((InStr(CStr(nz([NumberField])),CStr(55)))>0));
 

The NZ() is only needed when the number can be empty (Null)

Nic;o)