(Hello again).
It depends on what your definition of "affordable" is. The mega-resorts in the Kohala area in the northwest area, north from Kailua-Kona are almost all isolated 5-star luxury resorts in the middle of lava fields. As the other answerer said, the Hilton Waikoloa is fun, having little boat canals that can take you from point to point within the resort. It is one of the least expensive in the Kohala area, compared to the Four Seasons Hualalai, the Mauna Lani, Mauna Kea, etc.
If you are going to the Big Island to tour, these are a bit far from the sites and also are expensive. My recommendation is to stay in Kailua-Kona or slightly south in the suburb of Keauhou. There is also a micro-climate in this area that makes it lush and tropical...further north in Kohala is dry and barren; I still don't understand why they put luxury mega-resorts up there!!
I think the best buy right now is the 4-star Keauhou Beach Resort. It is in a pretty setting near some archeological sites, has a small (rare!) beach next to it. Service is great and I think it is glamorous for the price.
http://www.keauhoubeachresort-hawaii.com/?src=TIG-KBR-PPC-April012009-Google-KeauhouBrand-Breakfast
If you want to spend a bit more, I've also stayed at the 4-star Sheraton Keauhou Bay Resort which used to be the 5-star Kona Surf. This is really glamorous (if slightly dated looking like 1960-style Acapulco resorts), sitting on a solid black lava outcropping on the south edge of Keauhou Bay. Service is only average, but at night this is where the manta rays come to shore, attracted by the plankton which is attracted to the lights the resort shines on the water. Warning: You have to pay for parking your car here, and other "added optional fees" so beware.
http://www.sheratonkeauhou.com/
If you want to be near the small town of Kailua-Kona in a fun location, the King Kamehameha Kona Beach Hotel FINALLY reopened, and thank God prices are only a bit higher. Lots of locals like staying here because it was clean and a great buy. This is a great little town you will enjoy.
http://www.konabeachhotel.com/
If you will be hiking in the rain forest around Hilo and visiting the volcano (and I know you will based on your other question!) you may want to spend one or two nights on the other (eastern) side of the island so you don't have to drive back-and-forth 3-4 hours each way, each day. There really are no good hotels on this side, but my brother just stayed in Hilo at the Castle Hilo Hotel for $100/night, thought it was average..and this is the best hotel in Hilo. If you check tripadvisor, they will say that the tiny Dolphin Bay Hotel has better ratings now (and is cheaper) and frankly that is where I am going to book the next time.
http://www.dolphinbayhotel.com/