Thanks for all the replies, guys. Now, one by one...
It REALLY helps to have support from others.
When I get sick of mapping, I release screenshots of the WIP, post it on forums, invite my friends to play a private game on it, etc
Or you know, play the game that got you into mapping
The one thing that's kept me going and made me really want to keep working on my map has been playtesting and feedback. The main reason I end up opening up hammer is because there's a new suggestion for something I should do that I want to try out.
Well, that's what usually gets me going for a week or so, but then it just dies again. We have a small community of Bulgarian gamers, two of my friends regularly run servers for us to play by ourselves (we're about 15-20 people), and they run my map's tests whenever I ask them to... we've played the crap out of it. Yes, usually after we finish a test and I'm sitting there with a paper full of notes on what to do (I always write them down like that, found it very useful), I want to start Hammer and do it, but it doesn't last much longer...
I find that incorporating your mapping activities into a daily routine helps tremendously. I've been working on my second map ever, and my first in Source, a TF2 map, for almost a year now. If that isn't a test of motivation, I don't know what is. I made every newbie mistake in the book along the way which is why it's taken so long, but the fact that 9-11 PM every weeknight was 'map time' kept me going without me casting too much doubt on it.
Not everyone is a routine type of person, but if you can manage it I'd absolutely recommend giving it a go.
Well, I guess I'll have to try that - I'm not exactly a routine guy, but I can keep to a schedule when that's needed.
Staying motivated is one of those things which many people struggle with regardless of what task is at hand.
For me I stay motivated by working on multiple projects at once. If I get bored of one project then it's not a problem as I can simply move onto something else which interests me more. This method may not work for everybody (trust me, I have a lot of spare time to work on multiple tasks) but it worked for me and I was in the same position as yourself; working on something for a couple of days then giving up!
From a mapping point of view I think it would be best for you to start small, get feedback on progress and use it to feed personal motivation and push yourself to the finish line. Somebody else encouraging you to work more on your map is a lot better then keeping it to yourself until it's absolutely perfect.
On a side note, we can all aspire to produce maps of the same quality as the best mappers out there but you can't compare yourself to somebody who has more technical or artistic ability than yourself. It's an unfair comparison and there isn't any need to beat yourself up about it.
Yes, I've found that switching projects is what's worked best for me so far. The thing is, often the new projects steal most of my attention and I get completely involved in them. For example, I started programming a Tetris game (coding is probably my favourite design-related thing, followed closely by mapping) as a side-project to get me away from mapping (and eventually back into it). And now, I constantly want to work on the program and not the map :/
My current project has been motivated by it's theme/environment I can't wait to build (I plan for it to be really neat), and my plan is to stay in dev textures until the gameplay is worked out and not tell anyone about the theme I have planned. That way I always have the goal of getting to the fun, awesome part of the mapping once I push through the frustrating. (Though having motivation doesn't mean you always get work done... darn mappers block

)
My new environment is actually what got me wishing to get back into mapping after such a long pause in the first place. (Last time I mapped before TF2 was 2000-2004). My mates really liked it, I kind of liked it, I'm more or less done with the layout - which I also find the frustrating part - but now most of the "cooler" detail work is done, I'm left with the more annoying things, like fixing small details, redoing all my cliffs (because they suck.), etc.
story of my life. call it ADD or whatever, i have a HUGE problem with exactly what you're talking about.
the only things i've found to help are #1 - keeping the projects small. the smaller the project, the more likely i'll get to the end before i burn out or get distracted. #2 - collaboration. if someone else is involved in the project, i can hand it off to them when i get stuck, and when they hand it back, they've done something with it, and its new and different now, and i get motivated to work on it again.
Collaboration actually sounds like a nice thing, I got an offer by a fellow mapper some time ago. But I turned him down, because I was afraid I'd get bored and abandon the project - and I didn't want to let someone down. I guess I should see if the offer is still up though.
I find mapping fun, simple as that. If I didn't I wouldn't do it.
If you don't find it fun, stop. If you find doing little bits of maps and then moving on more fun than sticking it out with one map, do that.
Unless you actually do (or want to do) mapping professionally the #1 priority when mapping is to enjoy it, it is after all a hobby. But then if you want to do it professionally you'll find it fun anyway so that isn't a problem...
To tell you the truth, I also find it fun. I still somehow find it fun, regardless of the lack of motivation. It's hard to explain. I still get all those ideas in my head, it's just that I can't get to realising them.
About the last part, I'd have to disagree... sort of. For me, doing something professionally makes it stop being fun. Instantly. The fact that I'm doing it for the money, etc, just kills it for me... but I guess people see things differently.
Ohh, thought of another good one sort of related to the routine thing. One thing I do to cap off a night's work, and yes this can be seen as a questionable way to use your time to some, but I basically do a 'nightly build'. I run VBSP and VRAD only so it gets done in a reasonable period of time (though my near finished map is getting a bit long for this type of thing, compile time is roughly 45 mins) so every night I'm rewarded for my work by getting to explore my map with nice pretty lighting.
While it's compiling, I go do something fun like watch tv or play a game or read a book or something to unwind, then come back and take in my progress when it's done. Everyone's different though, it helps to experiment with different ways ( I for one can't juggle multiple projects well since I'd probably end up never coming back to the old ones, though there are times I REALLLLLY want to start a new map ).
Well, that worked a little in the beginning, but ever since I found that one simple hint brush downed my compile time from about 2 hours to 7 minutes (!), it doesn't do it anymore
Wow, did I write all that? Damn, and I still have time to catch the bus to uni... well, I guess I'm off then

I'll try to work for 2 straight hours on my map when I get home, we'll see how that works out.
Sorry if I missed anyone in the reply - it wasn't intentional!
Cheers mates!