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 = 1326 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 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 = 1326 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 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 = 1326 AND i.igate IN ('usahud','mnhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'mnhud', 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;

298 Church Ave
Esko, MN 55733

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $779,500
Beds: 3
Baths: Full: 1, ¾: 2
Status: Active
SqFt: 3824 Square Feet
Agency: Welcome Home Realty
Agency Phone: (218) 878-7970

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 9 acre(s)
Property Type: Residential-Other
Year Built: 2005
Notes: WOW! Everything about this home is stunning. You will be impressed from the minute you pull in the driveway. Not only is it stunning, but it's so quiet and secluded that it gives you that privacy to feel like you are finally able to relax. This home is a retreat. You will have plenty of space to spread out in this place. Entertaining will be a breeze with the large decks, open concept living area and all the yard on top of that. The fireplaces is a great focal point and can be used to snuggle up on the cold winter nights. The natural lighting is stunning and gives you that cozy feel. The kitchen has a great bar area to sit at, and a butler's pantry to help contain the mess of the day, or while hosting. Set up your bar area with snacks and drinks, and your guests will never want to leave. The loft area features an Outhouse that makes you want to stay Inhouse! The huge tub is the best place to soak away the day. If you are more of a sauna person, great, because you'll have that too. The loft will give you some flex space to add a bedroom area, guest room, family room, exercise area, or an office. There are so many options. The garage outside will make you want to split your time with the outside. The oversized garage has a kennel area, huge work area, is heated, and an overhead storage area as well. It leaves nothing to be desired. Home also has a great garden area, chicken coop area, and fire pit area too. A home like this with acreage and a highly desired ESKO school district will make you want this to be your forever house. It really is hard to have all these amenities, on such a great piece of land that looks actually park like. Call today for a private showing.
MlsNumber: --
ListingId: 6114889


Listing Provided By: Welcome Home Realty, original listing
Phone: (218) 428-0054
Office Phone: (218) 878-7970
Agent Name: Nikki Crotteau
Disclaimer: Copyright © 2024 Lake Superior 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