String Formation

[TheEntertainer]

Morning all,

I've been asked by a colleague to write an exception report. This is a type of report we run that helps to check that all the data that should appear on the client report does. The exception report that they want however is a report which checks the make up of a string, which should be 8 characters long (which is easy enough) but the first 2 characters should be numbers, the next 3 letters and the final 3 number ie 00LLL123, but it could be any numbers or letters. How do I test for this?

I'm using Crystal 8 (although the people who will be running the report have 7) on an Oracle 7.3 database

Thanks in advance.

TheEntertainer

 

[dgillz]

Create 3 formula fields as follows:

IsNumeric(Left({fieldname},2))
Not IsNumeric(Mid({Fieldname},4,3})
IsNumeric(Right({fieldname},3))

Then do a record selection formula that test if any of these three fields are false. This will show anything that does not fit the guidelines you mentioned.

There is one issue however, if the middle part of teh string is for example "Q3S", the middle formula above will return TRUE - the string is non-numeric. Is this OK? If not you can write a 3 line if-then-else formula that returns FALSE if any individual charater in the middle substring is a number, and TRUE if it fails all three tests.


Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[TheEntertainer]

Thanks dgillz,

But the isnumeric doesn't seem to be a function in v7 of Crystal, can you give me an example of the if then else statement or another function I could use in v7

Thanks again

 

[alley211]

Hi,

I have version 7 on my computer and I just tested this and it works:

Replace isnumeric with numerictext

numerictext(Left({@dfdfdfdfdfdf},2))
Not Numerictext(Mid({@dfdfdfdfdfdf},4,3))
Numerictext(Right({@dfdfdfdfdfdf},3))

alley

 

[TheEntertainer]

dgillz/alley211,

It works a treat. I've got a couple of alternatives I can use now depending on the version of Crystal.

Thanks alot