"Tweener" usually refers to defensive players. On offense, they'd call a guy caught between roles "versatile." It's usually seen as a good thing on offense because being versatile can help force mismatches and unless the offensive player is a lineman, his being overmatched by position mismatch won't kill the team.
The only tweener label guys Seattle drafted in recent memory who worked out well were Brandon Mebane and Red Bryant. I always thought Mebane was a really safe pick as a 1 tech anyway so his tweener label was dubious, IMO. And Bryant only found a degree of success when paired with a visionary head coach.
Typically, if a defensive player is labeled a tweener, say a guy like Dee Ford, their stock suffers and in some cases teams will take them completely off their boards. Coaches would much rather have a guy who is elite at one job rather than being passable at best for two jobs, usually only one of which is needed by that team.
Tweeners are usually bad bets, the exceptions are the Justin Tucks and Michael Bennett's who have the rare ability to be outstanding at multiple positions, or the guys like Mebane who aren't really tweeners at all but are miscast as such because of surface level details such as body size or usage.