Help finding text in field by query

[mrdavidking]

Hi I am a bit stuck, trying to find certain text by query in a text field that varies in length.
It is a horse breeding database, I already have the sire which comes first, I want to get the Dam which is after the hyphen but before the Dam's sire in brackets. Trouble is it is variable.

Example1: Southern Image (USA) - Djel D'Amour (by Dehere (USA))
I want only Djel D'Amour with no leading or trailing spaces.

Example2: lamosa (NZ) - Cinder Bella (NZ) (by Victory Dance (IRE (rest is cut off from source)
I'd like Cinder Bella (NZ) with no leading or trailing spaces.

I've dabbled with Instr, Mid and Len, but can't seem to get it right.
Any help greatly appreciated.


Regards,
David.

[alansidman]

This video will show you how to use the Instr function to achieve what you are looking for.

http://www.datapigtechnologies.com/f...rfunction.html

[mrdavidking]

Thank you alansidman, sorry for the delay in replying, it did help to some extent and I got the Dam, but can't remove the Dam's Sire. e.g. Djel D'Amour (by Dehere (USA)), but I'm happy enough with that. Thanks a lot for your help.
Regards,
David.

[ArviLaanemets]

Your problem is that you have same brackets to mark 2 types of information - country for any horse, and dame's sire.

Probable solution?
As the list of countries is probably not very long, replace brackets around country code with some other character - p.e. [USA], [IRL], etc.. You can use Replace() function for this. When you want to preserve old column as it is, add a new column, copy original data into it, and run a procedure, which cycles through all country codes and makes replacements. After that you can use InStr() in your query to get info from modified column.

When this isn't an one-time task then it is very bad idea to have several layers of information in same field. When this is the case, then my advice is to consider another database structure. Maybe something like
HorseID, HorseName, SireID, DameID, CountryCode, ...
Where HorseID is unique identifier for given horse, SireID is unique identifier for its sire in same table, and DameID is unique identifier for its dame in same table.