The N900 is capable of taking very detailed pictures under good lighting conditions. Take a look on Flickr or Google to find some of the excellent shots that can be taken in the hands of a capable photographer.

It's obviously no replacement for a good SLR, but I've been extremely pleasantly surprised by the quality of pictures the N900 can take, especially close-up subjects ('macro'). The other creative opportunity they open up is that the small size of the phone allows you take photos from unusual angles that aren't possible with an SLR.

The main things that cause loss of detail/blur in photos are:
1) wide apertures (used under low light conditions, to reduce exposure times for moving subject/camera, or creatively) will blur detail of anything not in the focal plane.
2) motion blur from the camera or subject moving or shaking.
3) high sensor noise relative to image signal - low light conditions reduce the strength of the image signal and hot sensors have much more noise (keep your camera cool before taking photos). Image processing to reduce/smooth out sensor noise to create a more appealing picture will usually sacrifice some picture detail ('real' signal) too.
4) optical defects can also add blur, which gets stronger towards the edges of the image, particularly in cheaper lenses.
5) diffraction also blurs detail. Diffraction increases as apertures/lenses get smaller and is a unavoidable side-effect of miniaturizing optics in phones.

The main thing you pay for in high-end SLR cameras and optics is their performance under challenging & low light conditions (which you clearly aren't going to match with a phone or compact camera). But by working within the limitations of the N900 under decent lighting conditions (avoiding low light, high contrast, glare, flare etc.) you can still take great photos.

If lighting conditions are challenging then there are some things you can do improve your photos:
1) keep your camera cool to reduce sensor noise.
2) keep the camera still to allow slower shutter speeds (and more light) - learn to hold the camera steady while squeezing the shutter, use structures/objects for additional stabilizing support if available, or use a tripod.
3) shield the lens from direct light outside the field of view.
4) post-process an uncompressed/raw image using noise-reduction then sharpening software. (Done well, this can make up for a stop or two of light in terms of final image quality / perceived detail-to-noise).