What you are describing sounds like you have a plugged catalytic converter causing an exhaust restriction. If the car won't accelerate and the RPM dont spike when the pedal is fully depressed, that is surely your problem. If it is that bad you should be able to feel the exhaust coming out of the tailpipe (or lack of it). The cat can also be temperature tested with a mechanics temp gun-it should be 100 degrees hotter at the exit than the inlet. Also manifold vacuum can be tested-if it drops under engine acceleration then it is an indication of restricted exhaust.
Let me know what you find out, I'll be glad to assist more if you need it.
Thanks for using JAMs Wrench
While Ms. Wrench has a good suggestion this is very unlikely on your car. Clogged converters almost NEVER occur on this car unless you ran it for a very long time with a misfire, you would also have a P0420 if this was the case. P0171 is 99.9% of the time a defective Mass air flow sensor on this car unless other codes are present also. The mass air flow sensor has a tendancy to do exactly as you say, does ok under normal loads but when you floor it the engine retard and almost seems to die-let off the gas a little and t spring back to life. I do recommend you also check the Throttle position sensor with a scan tool to make sure the on/off readings operate normally, this works in sync. with the MAF sensor but most likely this is a simple mass air flow sensor issue. Very simple repair a kid could do- 2 #2 phillips screws and an electrical connector. Part # XXXXX lists for $153.76
P0440-evap leak, you most likely left the gas cap loose or pulled the evap line loose on the air box when you replaced the MAF sensor.
P0420, as I stated earlier, converters don't just fail normally on this car, most likely you damaged the converter by driving it with the p0171 code, converter is now defective.