I was a SharePoint consultant/developer for quite some time and still do occasional projects.

The current version of SharePoint (2010) is a strong product if you stay with out-of-the-box features. It has all the major features you find in the collaboration-saas-app de jour just geared towards big business and therefore less "sexy". The core is still built around document management (versioning, corporate meta data/tagging and workflows being the major features) and integrates well into a Microsoft infrastructure environment. There are different levels of search, but if used correctly it can become the corporate search center and index pretty much anything you throw at it (Microsfot bought an external SharePoint search product a couple of years ago).

I agree with struppi the wiki (both the old and new versions) don't get close to functionality & usability of modern wikis.
But I disagree on page and site creation. The default templates cover most use cases. The page editing functionality is browser-based WYSIWYG that any Office user can figure out without involving the IT department (the big selling point). Sure you're not going to win design awards doing that, but you get the job done.

A lot of Microsoft Access functionality is replicated via lists, linked lists and views but without the concurrency nightmare.

Customizing the SharePoint design always fails from my point of view (apart from basic colors and a logo change). There are great examples but they require a lot of investment and who needs it for intranet sites. SharePoint _can_ be licensed for Internet use, but I have yet to meet someone who has done that - it's really an intranet product.

Development on SharePoint is a PITA. Workflow development is usually the starting point and has improved over the years but getting to version 1 is an uphill battle. Sadly it doesn't end there, the real problem is updating to the next version. Without going too deep into SharePoint terminology let's just say there is not "ALTER TABLE" you can run on SharePoint lists which makes changing data schemas difficult.

Regarding search: it does work out-of-the-box (even in the free foundation version) with one exception. To be able to search PDFs additional steps are required which is indeed a PITA to setup everytime. I'm assuming this has patent reasons. Search in SharePoint was one of my favorite features if you use it to the fullest extent.

Having said all that (and mostly because SharePoint always seems to be the big mystery product) I am NOT building my MVPs on SharePoint. I have built a couple of products on it but due to aforementioned update difficulties on the one hand and fairly easy integration of vanilla ASP.NET web applications into SharePoint environments I mostly choose that route for my products.

To sum up: SharePoint has improved greatly since 2003 and is a good collaboration platform for big (albeit conservative) businesses but cannot be recommended as development platform (for any kind of non trivial project). Since it is based on ASP.NET using that and integrating with SharePoint data via the API libraries is my suggested way to go.

PS: I agree with eletrichead that the market for SharePoint products is large and SharePoint knowledge is a value resource at the moment.