As long as you get a good lens, you'll be just fine with an ultrazoom style lens as a main all purpose lens...it's like any other kind of lens - there are better ones, and not so good ones. Don't worry about people saying the lens isn't that good just because of the long range it covers - they can be downright excellent lenses, supersharp, very flexible, and can be used in nearly any type of photography you care to try. There will be better lenses for a specific type of photography, but for overall versatility and still great image quality a good ultrazoom lens will be far better than any P&S camera.
For some samples of my 18-250mm lens (mine is labeled 'Sony', but optically is the same as the Tamron 18-250) on my Sony DSLR-A550 and A300 camera, feel free to take a look in this gallery:
http://www.pbase.com/zackiedawg/sony_sal18250mm_f3563
You can see I shoot everything with it - architecture, indoor, outdoor, landscape, macro, night photography, high ISO, action, wildlife. I do have 6 other lenses, and do use different lenses sometimes for a specific need...but for a one-lens, all-purpose solution, the 18-250 is always my default, go-to lens.