Record Selection 1

[MarkR2207]

Good Morning,

I have a field that contains first name records. I am atttempting to create a report that shows all records that have the first initial instead of the complete first name. I have seen somthing similar done before however I have forgotten how it was done. Any help would be greatly appreciated.


Thanks

 

[MJRBIM]

MarkR2207 -

Probably the best way to handle this is with a formula like :

Length ({TABLE.FirstName})

It will return the length of the First Name field then you can sort and suppress based on the results.

Hope it helps.

 

[crystalreporting]

So you only want to find records that have an initial in the first name, rather than a complete first name e.g. P instead of Peter?

If that's the case, then your criteria needs to use the Length function;

Length({table.firstname}) = 1

Peter Shirley

http://www.linkcorp.com

 

[MJRBIM]

MarkR2207 -

Just a thought...you are only as good as your source data - so look out for operators using PERIODS or DOUBLE-INITALS -->

EG : "M." or "A.J." or "AJ"

These would return length counts higher than 1, but may still want to be supressed by you.

Soemthing to keep in mind.

 

[GJParker]

If you only want records where the first name starts with a certain character then you can enter this in the Record Selection Formula.

{TABLE.FirstName} Like ?FirstInitial & "*"

Where ?FirstInitial is a string parameter entered at runtime

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[crystalreporting]

MJRBIM makes a good point - depending on who entered the peoples names into your database, you may have values that are single characters, or single characters and a period (e.g. P and P.). Maybe you could change your criteria to be;

Length({table.firstname}) < 3

I don't think there's many people out there with a first name less than 3 characters?

Peter Shirley

http://www.linkcorp.com

 

[synapsevampire]

I used to massage a lot of lists for this sort of thing, if I understand your requirement, try:

mid({table.field},2,1) in [&quot; &quot;,&quot;.&quot;]

You can elaborate on this to check for lengths, invalid characters, etc., depending on the quality of your data.

mid({table.field},2,1) in [&quot; &quot;,&quot;.&quot;]
or
len(trim({table.field})) < 3
or
isnull({table.field})
or
etc...

-k

 

[GJParker]

Oops !!

Think I completely misunderstood this one.

Hey Peter what about the dept head Jo and his staff members Al and Si





Gary Parker
Systems Support Analyst
Manchester, England

 

[lbass]

You could try the following formula {@idinit}:

if length(trim(replace({@initials},&quot;.&quot;,&quot; &quot)) = 1 or
(length(trim(replace({@initials},&quot;.&quot;,&quot; &quot)) <= 3 and
right(trim(replace({@initials},&quot;.&quot;,&quot; &quot),1) =
right(trim(replace(uppercase({@initials}),&quot;.&quot;,&quot; &quot),1)) then
1 else 0

For this to detect case for the second letter, you must go to file->report options and uncheck &quot;Case Insensitive SQL Data.&quot;

Special note: Because I have had trouble with the replace function in 8.0, I used &quot; &quot; instead of &quot;&quot; in the above formula, and therefore had to use &quot;3&quot; instead of &quot;2&quot; for initials like &quot;A.L.&quot; which would translate to &quot;A L&quot; after use of the replace and trim functions. In 8.0 using &quot;&quot; with replace results in a null.

This formula should work for &quot;A.L.&quot; &quot;AL&quot; &quot;A.L&quot; &quot;A.&quot; &quot;A&quot; --it would not recognize &quot;Al&quot; as representing initials.

The final step is to use the following in your record selection statement:

{@idinit} = 1

-LB

 

[MarkR2207]

Wow!!! Thanks for all the quick responses & good suggestions. This was the first time that I have used this site and plan to use it again.