select records that start with a specific character and end with a specific character 1

[blueboyz]

I am using Quantum 2013 and Crystal Reports 2008. The database engine is Pervasive SQL.

I created a report to display the sales for customers for a specific date range.
I need my selection criteria to include all customers whose ID startswith "GC", but doesn't end with "EX" or "EXEMPT".
For example a customer id like GCHUNTER would be selected for the report but GCHOLLYEX would not be included in the report.

Currently my selection formula is:

{Customers.CustomerID} startswith "GC" or (not({Customers.CustomerID} in ["ex","exempt"]))

This formula returns the records for all customers whether they start with "GC" or not and it also includes those customers whose ID ends in "EX" or "EXEMPT".

How can I change the formula so it only pulls the customer ids that start with "GC" and doesn't pull any customers that (start with "GC") end with "EX" or "EXEMPT"?

Thank you for your help.

 

[pmax9999]

Try:

{Customers.CustomerID} startswith "GC" and
(
	not({Customers.CustomerID} like "*ex") or
	not({Customers.CustomerID} like "*exempt")
)

Cheers
Pete

 

[blueboyz]

I tried the code above:

{Customers.CustomerID} startswith "GC" and
(
not({Customers.CustomerID} like "*ex") or
not({Customers.CustomerID} like "*exempt")
)

and I also tried:

{Customers.CustomerID} startswith "GC" and
(
not({Customers.CustomerID} like "*ex") or
not({Customers.CustomerID} like "*exempt") or
not({Customers.CustomerID} like "*EX") or
not({Customers.CustomerID} like "*EXEMPT")
)



but the report returned data for even those customers whose IDs were GCHENTONEX and GCHOLLANDEXEMPT.

There has got to be a way to exclude customers that have ID's ending in "EX" or "EXEMPT".

Any ideas?

 

[Charliy]

and right({Customers.CustomerID},2) <> 'EX'
and right({Customers.CustomerID},6) <> 'EXEMPT'

 

[blueboyz]

Thank you, that last code was the ticket!:

{Customers.CustomerID} startswith "GC" and
(
and right({Customers.CustomerID},2) <> 'EX'
and right({Customers.CustomerID},6) <> 'EXEMPT'
)

The report now picks out customers with IDs that startwith GC, but don't end in "EX" or "EXEMPT".