Title Page Background
DEBUG FN args: Array ( [0] => 3381 [1] => [2] => [3] => agent ) BEFORE: Site: 3381 LocID: Region: State: agent Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 3381 Igate: njhud LocID: 3381 Region: State: Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 3381 AND i.igate IN ('dehud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'dehud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 3381 [1] => [2] => [3] => lender ) BEFORE: Site: 3381 LocID: Region: State: lender Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 3381 Igate: njhud LocID: 3381 Region: State: Experttype: lender Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 3381 AND i.igate IN ('dehud','ushud') AND m.expertType = 'lender' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'dehud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1; DEBUG FN args: Array ( [0] => 3381 [1] => [2] => [3] => other ) BEFORE: Site: 3381 LocID: Region: State: other Experttype: Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 SITE IS NUMERIC, SWAPPING IFELSE numeric IFELSE else AFTER: Site: 3381 Igate: njhud LocID: 3381 Region: State: Experttype: other Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 3381 AND i.igate IN ('dehud','ushud') AND m.expertType = 'other' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'dehud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;

157 Hammersmith Way
Bear, DE 19701

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $799,999
Beds: 5
Baths: Full: 4, ½: 1
Status: Active
SqFt: 3300 Square Feet
Agency: BHHS Fox & Roach Hockessin-Pike Creek Home Marketing
Agency Phone: (302) 999-9999

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 acre(s)
Property Type: Other-Single Family Residence
Year Built: 2020
Notes: Rarely Available Smart Home in Prime Location! Welcome to this stunning 5-bedroom, 4.5-bathroom Wi-Fi Certified Smart Home, built just 5 years ago and filled with high-tech features and energy-saving upgrades. Step inside to discover gleaming hardwood floors throughout the main level, 9-foot ceilings, and an open-concept layout designed for today's lifestyle. The heart of the home is the gourmet kitchen, a chef's dream featuring upgraded 42' shaker cabinets with crown molding, a double wall oven, a built-in microwave, and elegant finishes. The kitchen flows seamlessly into the open-concept great room, perfect for entertaining. Adjacent to the great room is a convenient first-floor in-law suite, offering flexible living options for guests or multi-generational living. Upstairs, the spacious primary bedroom provides a true retreat with a large walk-in closet and a luxurious 5-piece upgraded bathroom oasis. You'll also find a versatile loft perfect for a home office or kids' play area, along with three additional oversized bedrooms, each with large closets. Enjoy tile flooring in all bathrooms and the laundry room. The unfinished dry basement with 9-foot walls and rough-in plumbing is ready for your custom finishing'ideal for additional living space, a gym, or extra storage. Stay comfortable and save on energy with a 90% energy-efficient gas furnace and a dual-zone HVAC system.Located in the award-winning Appoquinimink School District, this home sits in a prime neighborhood location, within walking distance to Lums Pond State Park, and close to shopping, dining, and entertainment. Don't miss this rare opportunity'schedule your private tour today!
MlsNumber: --
ListingId: DENC2080168


Listing Provided By: BHHS Fox & Roach Hockessin-Pike Creek Home Marketing, original listing
Phone: (302) 766-2373
Office Phone: (302) 999-9999
Agent Name: Judy Chen
Disclaimer: Copyright © 2025 Berkshire Hathaway HomeServices Fox & Roach, Realtors. 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

DEBUG FN args: Array ( [0] => njhud [1] => 0 [2] => new-castle-newark [3] => DE [4] => agent [5] => 1 ) BEFORE: Site: njhud LocID: 0 Region: new-castle-newark State: DE Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 IFELSE else AFTER: Site: njhud Igate: njhud LocID: 0 Region: new-castle-newark State: DE Experttype: agent Expertnum: 1 ProcessOverride: 1 SearchNearby: DisplayCount: 1 QUERY: 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 = 3381 AND i.igate IN ('dehud','ushud') AND m.expertType = 'agent' ORDER BY i.igate_index DESC, IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC, IF(i.igate = 'dehud', 0, 1), IF(i.igate = 'ushud', 0, 1) LIMIT 0, 1;
Real Estate Expert Photo for Sabri Thompson
Sabri Thompson
ASCIIThompson Realty Group
Call Today!: (302) 561-1717

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App