'Like' statement not working in Select Expert 2

[matpj]

hi,

I have a table I need to include in my report.
A field on this table (table2) is called RES_NAME.
The names are all <firstname> <lastname> (thats two spaces inbetween)

I need to match this field up with two fields from the other table.
The other tabe (Table1) has table1.firstname and table2.lastname

I have tried the following:
{TABLE2.RES_NAME} like "%{table1.firstname}%{table2.lastname}%"


this doesn't work. can anyone tell me what I am doing wrong?

 

[lbass]

Have you tried:

{TABLE2.RES_NAME} = {table1.firstname} + " " + {table1.lastname}

-LB

 

[matpj]

thanks LB,
that worked.

there is another factor that I must take into consideration also.

Occasionally in TABLE2 the RES_NAME will be in the format:
<firstname> <Middle Initial> <lastname>

e.g.
Paul G Martin (these only have single spaces in between)
do you know how I can incorporate this.

I will still be matching with just a firstname and lastname field from TABLE1.

 

[lbass]

Try something like:

left({TABLE2.RES_NAME}, instr({TABLE2.RES_NAME}," ")-1)+" "+mid({TABLE2.RES_NAME},instrrev({TABLE2.RES_NAME}," ")+1) = {table1.firstname} + " " + {table1.lastname}

This should work with or without initials.

-LB

 

[matpj]

do you know if that would work with no initials and only 1 space inbetween??

 

[matpj]

I have entered this after TABLE2.RES_NAME =

but when I click ok I get the message:

the remaining text does not appear to be part of the formula
any ideas?

 

[lbass]

Please lay out all possible scenarios from the beginning. It is frustrating to provide a solution only to find out that the problem has changed and requires a different solution.

I think you can just change the formula to:

left({TABLE2.RES_NAME}, instr({TABLE2.RES_NAME}," ")-1)+" "+mid({TABLE2.RES_NAME},instrrev({TABLE2.RES_NAME}," ")+1) = {table1.firstname} + " " + {table1.lastname}

This is the formula in its entirety. You do not need to add anything to it.

-LB

 

[matpj]

sorry LB,

I didn;t actually realise they were different until I ran it, and the results didn't tie up with the spreadsheet I had.
after a lot of looking around I discovered that some of the names only have one space.
Very annoying, and i'm not sure how the names are entered.

i'm still getting a problem.
I substituted the table1 and table2 with their actual names (just thought I would simplify it on here!)

my complete selection formula for the report is now:

left({(MSP2003_MSP_RESOURCE.RES_NAME}, instr({(MSP2003_MSP_RESOURCE.RES_NAME}," ")-1)+" "+mid({(MSP2003_MSP_RESOURCE.RES_NAME},instrrev({(MSP2003_MSP_RESOURCE.RES_NAME}," ")+1) = {dbo_employees.firstname} + " " + {dbo_employees.lastname}) and
{Proj_PM.PM} = {?Manager} and
{dbo_ppa_WIP.TransDate} = {?Date}

and I get the message field name not known.
I appreciate your help.

 

[Ngolem]

try this for a different approach

{table1.firstname} in {TABLE2.RES_NAME} and
{table1.lastname} in {TABLE2.RES_NAME} ;



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Turkbear]

Hi,
The best formulas in the world cannot make up for bad data control..The variety of possible entries may make any 'generic' formula unusable.( what if no spaces are there, or 4, or a 2 letter Middle name, etc....?)

Can the data be cleaned?

 

[lbass]

You have parentheses inside the field name. Try:

left({MSP2003_MSP_RESOURCE.RES_NAME}, instr({MSP2003_MSP_RESOURCE.RES_NAME}," ")-1)+" "+mid({MSP2003_MSP_RESOURCE.RES_NAME},instrrev({MSP2003_MSP_RESOURCE.RES_NAME}," ")+1) = {dbo_employees.firstname} + " " + {dbo_employees.lastname} and
{Proj_PM.PM} = {?Manager} and
{dbo_ppa_WIP.TransDate} = {?Date}

-LB

 

[matpj]

thanks LB,
didn't see those parentheses!!