How to make an exception for a certain record

[trixiegrrl]

Hi,

This is likely a silly question, I just am having a hard time come up with the solution at the moment.

I have many reports which contain the following criteria:

not ({LAST_NAME} like "*Test*")

Unfortunately there are many records in our db which we cannot delete containing Test as the Last Name. Now, the unthinkable has happened - we have someone on staff with a Last Name of Testa. I can pull this person by ID#, I just am having a hard time thinking up how to exclude all people named Test, but include Testa.

Thanks for any help you can provide on this one.

 

[Madawc]

Do a boolian, something like

({LAST_NAME} like "*Test*")
and {LAST_NAME} <> "Testa"

I am assuming that the others have something be beside 'Test' in the last name, otherwise you could check for that. Say not @TestOrTesta in the selection.

A more general solution would be to use SPLIT, which would subdivide "Test Person One" and isolate "Test" as the first portion.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CR85user]

If the {LAST_NAME} field is 'Test' on any such blank records and 'Testa' in the individuals case then simply remove your wildcards OR test first for this individual.

When you perform an if then else statement then it will check each section in order and not re-evaluate.

For example if your record selection was:

If {LAST_NAME} = 'Testa' then
{table.date} in {?daterange}
else if not({LAST_NAME} like '*Test*' then
{table.date} in {?daterange}
else '' and
etc etc

Or - given that the name is 'Testa' you could just remove the trailing wildcard.

not ({LAST_NAME} like "*Test")

This would pick up:

John Test
Test
A Test

But not:

Testa
Test 2
Testing

I hope this helps clarify.

If you can give more information on the construction of your report we may be better able to help give an idea on how to tidy the report up a little.

'J

CR8.5 / CRXI - Discovering the impossible

 

[trixiegrrl]

Thanks for your response. I ended up using Madawc's solution, although I was noticing it doesn't pass it into the SQL query so the filtering happends locally.

CR85User's solution did not work for me as I have some records that have Acktest and it would not exclude those.

 

[CR85user]

Do the records you wish to exclude only have 'Test'?

Or do you also want to exlclude the 'Acktest' records?


Let me know which of the following you would NOT want selected and which you would want:

Test
Testa
Atest
Acktest
Testtesttest
Detest
Testing
Stestinger

Cheers

'J



CR8.5 / CRXI - Discovering the impossible

 

[trixiegrrl]

Want:
Testa

Don't want:
Test
Atest
Acktest
Testtesttest
Detest
Testing
Stestinger

 

[lbass]

Try:

ucase({Last_Name}) = "TESTA" or
not (ucase({LAST_NAME}) like "*TEST*")

-LB

 

[trixiegrrl]

Thanks lbass, that works and is easier than the other solution (making a formula and referencing it in the record seclection).

I was noticing it still doesn't get passed into the SQL Query Where clause. I know I can use a SQL expression with Upper to get it to pass in but I recall vaguely having problems with SQL Expressions in the past.

Any ideas?

 

[lbass]

Absolutely use a SQL expression. Shouldn't be a problem and it will allow it to pass to the SQL. Good catch.

-LB