Hello Everyone,
Relational Data Model.
I'm trying to create an optional prompt where the user can enter zip codes they would like to EXCLUDE from the results. Since they will be entering multiple five digit zips, I would like to use a wild card that says to exclude that five digit zip or any nine digit zip beginning with that five digit zip the user provides.
For example if the user enters 90210, I would like it to exclude the results 90210, 90210-0222, etc.
I've tried different versions of these but none seem to work:
[ZIP_CODE] not in '?Excluded_Zip?+%'
[ZIP_CODE] not in Starts With ?Excluded_Zip?
Thank you!
Try these:
[ZIP_CODE] not in ?Excluded_Zip?+'%'
[ZIP_CODE] not Starts With ?Excluded_Zip?
Thanks Doug. Unfortunately, the first example throws a parsing error. The second example works but only with one zip entered at a time. The users need the ability to paste in up to 1000 zip codes in an optional, multi-line, multi-select text box prompt. Usually requires the word "in" [ZIP_CODE] in ?Excluded_Zip?
I think dougp's first example should be [ZIP_CODE] not like ?Excluded_Zip?+'%'. Like allows for wildcard operators, but these are single select only afaik.
Ashley: Correct.
wwconslt: left([ZIP_CODE], 5) in ?Excluded_Zip?
This limits the user to entering 5-digit zip codes.
If LEFT doesn't work (you didn't say which RDBMS you are using), you'll need to use SUBSTR.
Thank you everyone. Doug your solution works perfectly--thank you!