Is Like/Not Like Formula

[leanneconnexions]

I am trying to select the postcode starting with BH1 but do not want for example BH10, BH11 etc. Afetr looking through dummies guides I am using the right expressions but it wont work! Any crystal experts out there? The formula i am using is below:

{CR_CLIENTS.Postcode} like "BH1*" and
{CR_CLIENTS.Postcode} not like ["BH10*","BH11*","BH12*","BH13*","BH14*","BH15*","BH16*","BH17*","BH18*","BH19*"]

 

[crystalvictim]

Hi,
I doubt this formula will work - at least with our CR8.5 it produces an error.
You might try something like:

{CR_CLIENTS.Postcode} like "BH1*" and
not (left({CR_CLIENTS.Postcode},4) in ["BH10","BH11","BH12","BH13","BH14","BH15","BH16","BH17","BH18","BH19"]);

Be aware that the '*' have been removed in the second condition. Also, using '{FIELDNAME} not like' won't work - same as '{FIELDNAME} not in', so you'll have to use:

not ({FIELDNAME} in ...)
or
not ({FIELDNAME} like ...)

Hope that helps,

 

[leanneconnexions]

Thanks for the advice, I have tried

{CR_CLIENTS.Postcode} like "BH1*" and
not (left({CR_CLIENTS.Postcode},4) in "BH10","BH11","BH12","BH13","BH14","BH15","BH16","BH17","BH18","BH19"

But it is still complaining about a bracket missing?? I cant see where?

 

[leanneconnexions]

Just put square brackets round to look like:{CR_CLIENTS.Postcode} like "BH1*" and
not (left({CR_CLIENTS.Postcode},4) in ["BH10","BH11","BH12","BH13","BH14","BH15","BH16","BH17","BH18","BH19"])

and it works!!!!!

Thank You