Are you looking for a program to edit videos or just to create effects? Depending on how much you want to spend then you could go for Adobe Premiere Elements 3 (there is a version 4, but some recent posts on here suggest it has problems). Or you could go for Adobe Premiere Pro 2 (a much more advanced editor with a steeper learning curve), again it is an older version but it can do everything and would be much cheaper than the newer CS3 version. Another program is Sony Vegas.

These are outright video editors with effects included. But if the effects are top of your list, then look at a program such as Adobe After Effects. You should go onto their respective websites and download a 30 day trial version of a few of the popular programs you see listed on this forum and try them out. That way you will have a better idea of what works for you and doesn't before paying for a full version of the software.

__________________
Better than a poke in the eye with a pointy stick any day...