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

2874 E Orchid
Oregon, IL 61061

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $499,000
Beds: 4
Baths: 4
Status: Pending
SqFt: 3800
Agency: REMAX OF ROCK VALLEY

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 1 acre(s)
Property Type: Residential
Year Built: --
Notes: PAMPERED PERFECTION Exquisite taste is evident in this stunning all brick custom built home sitting on a 1.95 acre wooded lot. There's 4 bedrooms, 3.5 baths, Over 2300 sq ft on the main floor plus the walkout lower level that is finished for even more entertaining. The quality of craftsmanship abounds throughout this custom built home! The exceptional sized living room with 12 ft high ceiling and floor to ceiling gas stone fireplace is a beautiful focal point. The gourmet kitchen offers loads of Cherry wood cabinets, Corian countertops, center island cooktop with breakfast bar, French doors in the kitchen that lead to the wrap around deck overlooking the inviting patio area that's great for entertaining or just relaxing! The inviting luxurious owner's suite accommodates the most lavish furnishings with French doors that lead to the deck, plus a huge private bath with walk in shower and jacuzzi tub and walk-in closet! You'll love your address as much as living in this exceptional home. The custom backyard with its own waterfall and pond with a private wooded backyard with impressive landscaping. The oversized 3.5 car garage offers extra storage. Fiber optics high speed internet for your streaming needs. You must inspect this one to appreciate it! This is definitely where you'll want to be! Located just off of Rt 64 with easy access to I-39 and I-88.You'll love this location with it being only a few minutes from downtown Oregon. There's 5 state parks nearby, the Rock River for fishing and boating fun and so much more to offer in the quaint community of Oregon.
MlsNumber: 202401244


Listing Provided By: REMAX OF ROCK VALLEY, original listing
Name: REMAX OF ROCK VALLEY
Phone: (815) 732-9100
Office Name: Re/Max of Rock Valley
Office Phone: (815) 732-9100
Agent Name: Carla Benesh
Disclaimer: Copyright © 2024 Rockford Area MLS. 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