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

205 Poplar Manor Avenue
Fredericksburg, VA 22406
MODOU CAMARA with Smart Realty, LLC, original listing - (301) 445-1395

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,030,000
Beds: 5
Baths: Full: 4
Status: Active
SqFt: 5828 Square Feet
Agency: Smart Realty, LLC
Agency Phone: (301) 445-1395

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 1 acre(s)
Property Type: Residential-Single Family Residence
Year Built: 2020
Notes: Welcome to this inviting, spacious and versatile 5-bedroom, 4-full-bathroom single-family Rambler home, nestled on a quiet generous street and idyllic lot!! in the highly sought-after Stafford county. This Rambler home with a huge finished basement offers versatile living arrangement and modern amenities. The main level features original hardwood flooring throughout, offering a classic touch to the generous-sized living and dining area. From the Sun room, step out onto a lovely deck, perfect for outdoor entertaining and relaxing while overlooking the beautifully landscaped, private backyard full of nature. The main level boasts 3 bedrooms 3 full baths.The fully finished basement has two bedrooms and a full bathroom, makes it a convenient use for family members and house guests. The finished basement serves as a flexible space, complete with its own separate entrance, a bar, game room, office space, huge family room and ample storage spaces. making it ideal for entertaining guests and perfect for movie nights. You'll also find plenty of storage throughout the home. Step outside to enjoy a beautifully maintained lawn with a serene garden, ideal for relaxation or entertaining. The following are upgrades: Wifi refridgerator; Wifi washer / dryer;Crystal chandelliers with ceiling fans;Two-Car detached garage toholding 4cars altogether;Automatic Solar lights for backyard lighting at night,Heavy duty Ceiling storage in garage;She-shed in back yard with Storage shelving;Storage shelving in detached garage;Gozebo and 2 firepits in backyard;Stone walkway to backward and special underground drainage for easy water run off;Fruit trees with arbor for kiwi vine;<br /> Retaining wall on side of the house for easy walking and grass cutting. this home offers easy access to top-rated schools, shopping, dining, and commuter routes. Don’t miss this opportunity to own a home that combines functionality, beauty, and location! Oooops? not to mention it's solar powered to save cost. MUST SEE!!! No for sale sign on yard.
MlsNumber: --
ListingId: VAST2033876


Listing Provided By: Smart Realty, LLC, original listing
Phone: (301) 445-1395
Office Phone: (301) 445-1395
Agent Name: MODOU CAMARA
Disclaimer: Copyright © 2024 Bright 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 = 2912 AND i.igate IN ('usahud','njhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'njhud', 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;
Alex Druash
ASCIICENTURY 21 Redwood Realty
Call Today!: (571) 302-0431

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App