Yes, the Northern Neck and Middle Peninsula are nice; I lived in Tappahannock for 4 years and it's great small town living. If you're on the water, even better. It's in Tidewater, meaning you're going from fresh to brackish to salt water (heading west to east as the rivers flow down to the Chesapeake Bay), and those parts of Va. have been popular for decades. Lots of demand from Washington-area retirees, military retirees from Hampton Roads, and people from central Va. looking for a place at the river. Real estate is not as cheap as other places.
My family has spent vacations near Clarksville, Va., on Buggs Island Lake (Kerr Lake), a dam separates it from Lake Gaston; the lakes are in both NC and Va. It's a man-made freshwater lake, not a coastal community. Great freshwater fishing. If you want something not touristy and not especially close to an urban area, this part of rural southern Va. is worth considering. Prices are probably better than the N. Neck, Mid. Peninsula waterfront properties.
We'd love to hear what you like, after you've looked around.