Tuesday, April 29, 2014

Crystal Reports : MID() and INSTR()

Okay, so the problem is thus; cut out the lastname of any given Contact Name (i.e. Justin Hill should return "Hill", etc). This is actually very easy to do using both INSTR() and MID()

The INSTR() function searches a string for another string. It takes two parameters, like this:

INSTR(string to search, what to search for)

We are going to use it to search for the space in our {USERS.NAME} field.

So, going back to our "Justin Hill" example,

INSTR({USERS.NAME}, " ") = 7 - finds the space between first and lastname

So going back to our original problem, cutting out the Lastname, we can now substitute the starting position in our MID() function with the INSTR() from above like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ")) returns " Hill" (note the leading space).

What you really need to do is increment our starting position by 1, because the Lastname starts one character position after the space. Like this:

MID({USERS.NAME}, INSTR({USERS.NAME}, " ") + 1) returns "Hill"

Because the MID function is always being passed the location of the space by INSTR it should reliably "kick out" the Lastname.

Then it is a simple matter to make this code into a Crystal Formula, then use that as your sort field in your Sort Expert.

1 comment:

Chanel Perfume Bottle said...

The USP of the YU smartphones is CyanogenMod OS, a highly customisable ROM of Android, a long-time favourite of hard-core geeks.