I cannot use Wild card "*" in conjunction with special character ")" in my filter, even i have used "@@".
My filter is as follows :
*-01)
I have already modified as *@@-01)
How should I type the filter to enable the above fitler ?
Thanks for your advice in advance.
The filter entered in cell B4 is *-01)
In your Jet Function Filter, use Excel's REPLACE and SEARCH functions.
This is what my test functioned looked like:
NL("rows","customer","Payment Terms Code","Payment Terms Code",REPLACE($B$4,SEARCH(")",$B$4),1,"?"))
Essentially, look for the ) and if you find it, replace it with ? which Jet will accept as a place holder for any character.
If you wanted to kind of error proof it so when a user enters something without a ), then you could take it one step further:
NL("rows","customer","Payment Terms Code","Payment Terms Code",IFERROR(REPLACE($B$4,SEARCH(")",$B$4),1,"?"),$B$4))
