0

I'm trying to exclude any value from a certain field (table.value) that does not match this format AA#####A. Example if they entered APT12345T, or PT12345PT and No Value then I want to exclude it from the report. It needs to match example AP12345P. What selection formula can I use to accomplish this. Any help is greatly appreciated Thanks in advance.

Beth
  • 9,531
  • 1
  • 24
  • 43
Dagz200
  • 221
  • 7
  • 15
  • 25
  • I've try using this but seems that it does not work the way I though it would work in regards of just bringing me just the format that I need it to be. Local StringVar ThisPart := {table.value}; While Len(ThisPart) > 8 length. Do ThisPart := ThisPart & " "; uppercase (ThisPart) ) – Dagz200 Sep 18 '12 at 15:40
  • OK, but I meant more specifically... Oracle, MSSQL, etc.? I ask because you'll want to use a SQL expression if you want the evaluation done on the DB side vs. locally, and this will be dependent on the syntax of your DB. – Ryan Sep 18 '12 at 15:49
  • You could also try a regular expression: [crystal reports - how to extract a date from string][1] [1]: http://stackoverflow.com/questions/11758273/crystal-reports-how-to-extract-a-date-from-string – craig Sep 18 '12 at 19:21

1 Answers1

1

try reading Crystal's help topics on the mid() and isnumeric() functions.

here's an example from the help file:

Examples The following example is applicable to both Basic and Crystal syntax:

Mid("abcdef", 3, 2)

Returns "cd".

so, in your case, you want to strip your value into three pieces,

mid(table.value,1,2)
mid(table.value,3,5) 
mid(table.value,8,1)

and build up a three-part boolean variable where:

  1. the first piece is not numeric(), or between 'AA' and 'ZZ', or however else you want to test for letters,
  2. the second part isnumeric(), and
  3. the third part passes the same test as the first part.

where are you getting stuck?

something like this:

not isnumeric(mid({table.field},1,2)) and
isnumeric(mid({table.field},3,5) and
not isnumeric(mid({table.field},8,1))
Beth
  • 9,531
  • 1
  • 24
  • 43
  • I did but could not find anything. Thanks tho. – Dagz200 Sep 18 '12 at 15:43
  • 1
    @Dagz200 Are you serious? You couldn't find any references to either the mid() or isnumeric() functions on the internet? – Ryan Sep 18 '12 at 15:52
  • I'm assuming he couldn't find anything similar enough to his problem, he couldn't apply the raw functions to solve his problem. – Beth Sep 18 '12 at 15:53
  • Yes I could not find anything specific to the problem. I'm sorry I'm new to crystal. – Dagz200 Sep 18 '12 at 15:55
  • @Beth So I should put it in a formula to then put it in the selction formula to say {@formula} = True would that work. Again I'm new and trying to understand how it all works. Thanks – Dagz200 Sep 18 '12 at 16:27
  • of you could put it directly into the selection criteria, whatever you can get to work – Beth Sep 18 '12 at 16:58