'Contains' problem

[silversurfer19]

I'm new to Access and I suspect I'm just demonstrating my lack of know-how, but here goes.

I've been given a table which holds a series of details about a business, including customer name[CUOR_NAME], which stores the whole name in one field. In the same table there are also other fields containing another company's data on the same address. They have the customer's names split into seperate fields, title, forename and surname, e.g. [ML_SE_SURNAME].

I am attempting to write a query which will check whether the [ML_SE_SURNAME] field, contains any substring of [CUOR_NAME], i.e. whether the surnames for the customers match. I've looked at using the contains function to do this but with no success, it keeps telling me the function doesn't exist, so I'm beginning to wonder if either it can't be used in Access or I'm just doing something wrong.

I apologise if I'm just wasting people's time, any help would be appreciated.

 

[dkinley]

Check into the Instr function, it might solve your problems. Here is is the idea except use your fields as the evaluation.

Specific notes though: If substring is empty, it returns the start value. If substring is null, it returns null. If there is no match, it returns 0 to help you properly prepare for the evaluation.

-dK

 

[silversurfer19]

Thanks, that works great.

 

[dkinley]

Good to go and good luck on your project.

-dK