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

15405 Main Road
Mattituck, NY 11952
Susan M. Orioli with Engel & Völkers North Fork, original listing - (631) 298-7953

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,399,000
Beds: 4
Baths: Full: 2
Status: Active
SqFt: --
Agency: Engel & Völkers North Fork
Agency Phone: (631) 298-7953

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 30,492 acre(s)
Garage: Garage
Property Type: Other-Single Family Residence
Year Built: 1765
Notes: Welcome to a truly exceptional property that masterfully combines the timeless allure of classic architecture with the comforts of modern living. This stunning home, built in the 1800s, showcases a wealth of character, highlighted by beautifully exposed beams that infuse warmth and charm into every room. Step inside to discover a completely updated interior that harmonizes historical elegance with contemporary style. Throughout the home, classic wide plank hardwood floors create a cohesive and inviting feel, while beautifully preserved vintage details, including salvaged tiles from past renovations, pay homage to the property's rich history. The spacious living room invites you to unwind beside the fireplace, creating a cozy atmosphere ideal for gatherings or quiet evenings. The heart of the home lies in the dining room and eat-in kitchen area, both designed with ample space to accommodate family gatherings and culinary adventures. Venture outside to discover a charming deck, perfect for outdoor entertaining or simply enjoying the serene surroundings of breathtaking specimen landscaping. The detached two-car garage provides ample space for vehicles, hobbies, or even a home gym. Plenty of room for an inground pool. Experience the unique charm of this historic home, nestled in a tranquil neighborhood, all while enjoying the conveniences of modern living. This home is meticulously updated and maintained. Don't miss this rare opportunity to own a piece of history!, Additional information: Appearance:Exquisite,Separate Hotwater Heater:Y
MlsNumber: --
ListingId: L3575647


Listing Provided By: Engel & Völkers North Fork, original listing
Phone: (631) 298-7953
Office Phone: (631) 298-7953
Agent Name: Susan M. Orioli
Disclaimer: Copyright © 2024 Engel & Völkers. 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