Both columns return "2". What does this tell you?

After my last post I woke up in the morning with a good idea (funny how that happens - bit of a worry really!). You suggested earlier to add "WHERE LEN(COL) > 4 ", which didn't work. I thought about capping the greater than with a less than and have had success. I am now using " WHERE LEN(COL)>0 AND LEN(COL)<6 ". This is working perfectly.

I have since changed the WHERE TYPE = 'ABC' to the other type I want, let's say 'XYZ', and it's failing again and I can't see why because the data is in the same format, e.g. 5, 1.5, 9.99. All very strange to me. In the meantime I accidentally discovered that it works by removing the join to the other table in the sub query, which was simply to exclude archived people. As this is not essential, as I can exclude them further in the process, I might just carry on like this. If the above, or your PARSENAME suggestion, has given you an answer I, and perhaps other readers, would be keen to hear your response. If none of this helps I will carry on with the first query that's working as is, and for the second one just exclude archived people.

If I don't hear from you, thank you very very much for your help - you have really saved the day! I hope others can benefit as well.