Several options, Change the Encryption method to not include special chars... (use Hashing methods)
Convert the encryption string to the ASCII Hexcodes and store those... Example:

encryption= Qw@$l)'
Store :517740246C2927

These are easy to compare, and still maintain the encryption integrity...

The main reason why it might work in the Query analyser is that it passes the query slightly differently to how VB6 would, also ARE YOU SURE you using exactly the same encrypted password that VB6 is trying to pass..

Something else to ponder...
Code:
update t_username set user_passwd = '[Password]' where user_name = '[Username]'
and
Code:
update t_username
set user_passwd = '[Password]' 
where user_name = '[Username]'
Are very different queries...

while they return the same results. The line breaks cause them to be processed differently..

and if there is some problem in lets say Password, like a single quote followed by comment marker these are the resulting Query's for the above two.

Code:
update t_username set user_passwd = '[Pass]' -- [word]' where user_name = '[Username]'
Code:
update t_username
set user_passwd = '[Pass]' -- [word]'  
where user_name = '[Username]'
as you can see with what i highlighted in green is now considered a SQL comment, and in the two query's, the comment parts are vastly different and change the final query...