Using a Wildcard on a like left Outer Join

[Hoganc]

Hi All

I have been running a query to find out the name and addresses of some of our customers but some data keeps coming in to the results that i dont want to see.

The data is in the Name column so I have built a second table and put the names of the items i do not wish to see in the second table using a left outer join and selecting null or not null. This works great if i know all the combinations of the name such as smith i.e d smith david smith, sam smith dd smith etc.

I would like to enter Smith into the table and have my query return everything except with the word smith in.

I can code this into the query with not like *smith* but that means everytime i want to go into it i would have to enter the query not the front end

Anyone any ideas?
Thanks

C

 

[GohDiamond]

Not Like Wildcard

in your query you could enter something like this as your criteria:

Not Like "*" & [Enter Last Name to Exclude] & "*"

This pops up a message asking for user input. With this particular wildcard configuration if one entered "smith" it would also eliminate names like Hammersmith and Smithfield as well as just Smith

Cheers!
Goh

 

[Hoganc]

Hi G

Thanks for that but I need to eliminate more than one name at a time hence the table of names but then thier wildcards would come after - it is because the system uses smith then some kind of customer number ?? aftwards and sometimes before..

how can i use the items on a table and then a wildcard?
Thanks
C

 

[neileg]

Your problem isn't helped by the way you are saving the name data. Clearly it would be easier if you had surnames separated from the customer number or whatever else there is. It's worth considering if this redesign is the most effective way to go.

 

[Hoganc]

Unfortunatly I cant - the data comes from another system that just pays customers out. which is why it has different details at the end i.e. a persons name smith and a customer number - so smith needs to be removed with all the versions of the customer or transaction number whichever has been put in to identify it. (yes variations have been used including dob)

I didnt design the first bit its old - so I need to be able to filter out by using a table and a wildcard function of some description i though i could do comethig like Like*&

![Name]&* but i cant get that to work?? so any ideas I am up for

Thanks