I need a report to pick up a field "No." from an "Item" table where No. = 1102* or 1202* but is not equal to a 20 character value beginning with 1102 and ending with 00. For example: 1102??????????????00.
The following does not work. The report picks up all No. fields with 1102??????????????00.=NL("Rows","Item","No.","No.","1102*|1202*&<>1102??????????????00")
I think the above example shows what I am attempting even though it does not work.
Does anyone have any suggestions? Can it be done with Jet Reports?
Thanks
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi David,
So Jet filtering in this case works like Nav filtering. You can't use wildcards such as * or ? with <> in Nav filtering, which is why your filter doesn't work. However, it is possible to achieve what you're trying to do using NP(Difference). The formula would look something like this:=NL("Rows",NP("Difference",NL("Filter","Item","No.","No.","1102*|1202*"),NL("Filter","Item","No.","No.","1102??????????????00")))
This formula will get all the item numbers starting with 1102 or 1202, then subtract from that set any item numbers which match your 20 character pattern. This will probably not be fast, but it should work. Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thank you, Hughes.
I just found out that I don't really need to filter out 1102??????????????00.
I have never used NP(Difference) but will have use for it on some other reports. Your answer turned out to be really helpful even though I don't need it for this particular report.
I do understand why wildcards will not work with <>. I was just using the code in my first post to make it clearer what I was after.
Thanks again.
Please sign in to leave a comment.