It seems (after reading the big threads where this has been talked quite much) IMO that N900 camera suffers from couple of things:

-bluish haze on top of pics
-->mainly because of blue rim around cam

-hazy spots
-->mainly because of silver rim and unfortunate luck with reflections, which means that this is hard to reproduce and just my guess

-Blueish&greenish overall color
-->Bad image algorithm on default cam app + low light
-->Flash

In the end and this has been said many times. Avoid flash as much as possible. It just ruins your photos. Taking pics in low light is something that you should in generally avoid with this device. You might get ok results with experimenting settings and using something static to keep your device still but probably the moment has already gone when you are ready with your n900

Also remember that shutter button has two states (half-press to focus ->green, and full to release shutter).

.edit
For reference to what N900 cam is capable :
http://talk.maemo.org/showthread.php?t=38660
(Notice how most of pics are taken in sunlight!)