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

20 Waverly Avenue
Smithtown, NY 11787
Christina Rieckehoff with Realty Connect USA L I Inc, original listing - (631) 881-5160

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,399,000
Beds: 4
Baths: Full: 3, ½: 1
Status: Active
SqFt: 3500 Square Feet
Agency: Realty Connect USA L I Inc
Agency Phone: (631) 881-5160

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 sqft
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2024
Notes: Welcome To This fabulous 4 bedroom, 3.5 Bath,3,500 Sq. Ft. Custom Built Farm Ranch In The Award Winning Smithtown School District. As You Enter Your Breath Will Be Taken Away By The Grandeur Of This Home.. The Kitchen Is A Chef's Delight W/Top Of The Line Stainless Steel Appliances, Full Size Refrigerator/ Freezer Wall, Quartz Counters with Quartz Backsplash, Oversized Custom Panty, Laundry Room.off kitchen. Spacious Formal dining room all w/Coffered Ceilings to Finish Out This Elegant Space. Extra large Family Living Room With Stone To Ceiling Gas Fireplace. This Open Floor Plan design Is Perfect For All Your Entertaining Needs. Primary Ensuite Is Located On The First Floor which could be Your Own Private Sanctuary. Full bath Designed With Oversized Tiles Throughout, oversized Shower Area, plus theres a huge Closet Waiting For You to add your Finishing Custom Touches. Additional Bedroom On The First Floor Has amble Closet Space And A Full Bathroom of its own. The Second Floor Boasts 2 Bedrooms, Beautiful Full Bathroom, Huge Double Linen Closet Is Located In The Hallway. There Is also an Unfinished Bonus Room Waiting For You To Customize As You Wish.. Hardwood Floors Throughout, Gas Heat, on demand state of the art hot water system, Gas Cooking, Central Air,8 Zone inground Sprinkler System and Brand new State Of The Art Septic System. Full 1,900 Sq Ft Unfinished Basement With Egress Window And Outside Entrance.. Plenty Of Parking In your own Private Driveway Along With A Double Car Garage With Extra High Ceilings. Close To Schools, Trains, Stores, Restaurants, Major Roads, And Parks. Taxes Are Approximate And Without Star Exemption. Builder Fees To Be Applied , Contact Your Agent For Fee Schedule.., Additional information: Appearance:Diamond+++,Cooling:SEER Rating 12+,Green Features:Insulated Doors
MlsNumber: --
ListingId: L3588731


Listing Provided By: Realty Connect USA L I Inc, original listing
Phone: (631) 881-5160
Office Phone: (631) 881-5160
Agent Name: Christina Rieckehoff
Disclaimer: Copyright © 2024 OneKey MLS. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


Local Real Estate Expert

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 = 3384 AND i.igate IN ('usahud','nyhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nyhud', 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;
Patrick Canty
ASCIIeXp Realty
Call Today!: (631) 943-9161

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App