select on a created concat field

[ChipF]

I have a formula that concats the last name and first name, now I want to select records where the last name matches and the first few letters of the first name match how would I do that in the selection formula?

concat = LastName+", "+FirstName

{@concat} like "BAUMGARTNER, SUS*

 

[bdreed35]

Create a SQL Expression field that does the concatenation on the server side.

SQL Expression something like this:

{fn CONCAT({fn CONCAT( table.lastname,", ")}, table.firstname )}

Replace your selection criteria with this:

{%concat} like "BAUMGARTNER, SUS*"


~Brian

 

[synapsevampire]

That depends on your database, how the data is stored and the version of Crystal.

The most efficient means is likely to use 2 fields to take advantage of indexes.

How is this name getting entered? You aren't showing any parameters, are they hardcoded?

Presumably you want the users to enter the name in the form <Last, First>, so try the following:

Select Insert->Field Object->Right click parameters and select New

Name it NAME, type String.

Select Insert->Field Object->Right click formulas and select New

Name it Last, type String

Place the following:

left({?Name},instr({?Name},",")-1)

Select Insert->Field Object->Right click formulas and select New

Name it First, type String

mid({?Name},instr({?Name},",")+1)

Select Report->Edit Selection Formula->Record

Place the following:

(
{table.last} = {@Last}
)
and
{table.first} like {@First}+"*"

Try supplying more information in your posts, the basics being:

Crystal version
Database used
Example Data
Expected Output

-k