You my friend have a collation problem. The problem is you are using accent sensitive collation. If you check your collation, it is probably SQL_Latin1_General_CP1_CI_AS. The problem with this collation is the last two letters "AS", which stands for accent sensitive. You will not be able to query the data as it stands, without changing the collation. I will provide you with a quick work around, but I will advise you to change your database collation. If you plan on storing this type of data, you do not want to have to "work around" to query it.
SELECT fname, lname
FROM members
--this is the standard MS collation, but is Accent Insensitive
WHERE lname = 'Hemond' COLLATE SQL_Latin1_General_CP1_CI_AI