Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1286 AND i.igate IN ('usahud','wahud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'wahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1286 AND i.igate IN ('usahud','wahud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'wahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 1286 AND i.igate IN ('usahud','wahud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'wahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

2900 Herron
Gwinn, MI 49841

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $199,900
Beds: 2
Baths: 1
Status: Pending
SqFt: 952
Agency: Select Realty

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 5 acre(s)
Property Type: Residential
Year Built: 2000
Notes: If you are looking for a great lakeside retreat, I've found it for you! This is a perfect & peaceful log home with the best views and the best sounds ever! Sounds? That's right, you'll hear nothing except the windchimes on the deck and the birds in the trees. This year round home was built with quality construction and nothing was overlooked, featuring 2 bedrooms; one on the main floor and one in the upper loft along with a full bath and main floor laundry. The kitchen has everything you'll need including new laminate countertops in 2023, newer LVT flooring 2022 and a great walk in pantry. You can't beat the views! After dinner retreat to the cozy living room with fireplace or step out onto the covered Trex deck overlooking the lake. Sauna anyone? Outside is a great wood burning sauna with changing room. Have lots of toys? There's plenty of room for storing them with 3 outdoor small garages and plenty of ATV trails right in your back yard for riding. Fishing anyone? You'll catch some great perch, bass, big bluegill and crappies and other sunfish right off your dock or from your pontoon boat (that will stay with the property!) Hunting? Plenty of state land close by and the deer frequent the property often! The property all around you is plentiful with maples, white birch and pine along with planted apple trees and a weeping willow down by the lake. There's a nice cleared spot at the top of the driveway for a future garage if you choose to build! The lake is quiet with only a select amount of seasonal or year round homes and the road is gated just to the residents so privacy is a plus but yet you are close enough to all that Gwinn has to offer!
MlsNumber: 50140194


Listing Provided By: Select Realty, original listing
Name: Select Realty
Phone: (906) 228-2772
Office Name: SELECT REALTY
Office Phone: (906) 228-2772
Agent Name: ANDREA GORIESKY
Disclaimer: Copyright © 2024 Upper Peninsula Association of Realtors. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App