asked on

Search using Like in crystal to find something that contains a letter followed by a number.

Basically I have a list of jobs. They can be in several similar formats and I want to group based on only one of these formats;

E12345-1-PLN
ENG-E12345-1
ENG-12452-1
15234-1-PLN

I need to be able to group these so that any Job that starts with an 'E' gets filtered into Electrical, but the ENG at the beginning of some of the job #'s willl throw off 'startswith'.

So in the list above I should only get the following couple of jobs;
E12345-1-PLN
ENG-E12345-1

Here is what I tried;

if {Command.JobNum} Like ["*E#*"] then "Electrical"
else "Main Shop"

So what symbol can I use to represent E followed by a number?

James0628

CR doesn't have a "numeric character" wildcard, at least as of CR 10. I suppose they may have added something in later versions. The only wildcard in CR 10 is "*", for "0 or more characters".

If you want anything that starts with "E", but not "ENG", to be in "Electrical", how about this:

if {Command.JobNum} like "E*" and not ({Command.JobNum} like "ENG*") then "Electrical"
else "Main Shop"

James

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Will

Try looking at this , you will probably need to modify it somewhat to eek out what you want.

uses instr , mid and isnumericfunction
test-group-by-alpha-followed-by-.rpt

vasto

what is your database ?

Jeremy Campbell

ASKER

Thanks guys.. I will check the proposed solutions here in a bit.. Got to wrap up something else first..

The database is SQL

vasto

SQL Server will give you more options to recognize the format.
http://msdn.microsoft.com/en-us/library/ms179859.aspx

This will work just in case your report is based on stored procedure.