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 = 3393 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 = 3393 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 = 3393 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;

5 Murray Rd
Hicksville, NY 11801
Joseph Lombardo with LOMBARDO HOMES & ESTATES, original listing - (516) 200-6448

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $958,888
Beds: 4
Baths: Full: 2, ½: 1
Status: Active
SqFt: 2690 Square Feet
Agency: LOMBARDO HOMES & ESTATES
Agency Phone: (516) 200-6448

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 0 sqft
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 2024
Notes: NEW CONSTRUCTION 2600 SQ. FT. 4 BEDROOM 3 BATH COLONIAL WITH A FULLY FINISHED BASEMENT AND A 1.5 CAR DETACHED GARAGE....STUNNING NEW CONSTRUCTION COLONIAL OFFERS A PERFECT BLEND OF MODERN LUXURY AND CLASSIC CHARM. METICULOUSLY REDESIGNED AND UPGRADED, THIS RESIDENCE BOASTS EXQUISITE FINISHES, THOUGHTFUL DESIGN ELEMENTS, AND AMPLE LIVING SPACE FOR THE DISCERNING BUYER......1ST FLOOR: LR, KIT, DR, FULL BATH, BACK EXTENSION WITH 2 MORE ROOMS (BEDROOM, FAMILY ROOM). 2ND FLOOR: 3 BEDS, 1 FULL BATH, OPEN OFFICE ROOM. THE BASEMENT IS FULLY FINISHED WITH A FULL BATH AND AN OUTSIDE ENTRANCE......CONTEMPORARY DESIGN::::::::: OPEN-CONCEPT FLOOR PLAN FLOODED WITH NATURAL LIGHT, ACCENTUATED BY HIGH CEILINGS, AND ADORNED WITH SLEEK HARDWOOD FLOORING THROUGHOUT. THE SPACIOUS LIVING AREAS ARE IDEAL FOR BOTH RELAXATION AND ENTERTAINMENT, OFFERING SEAMLESS FLOW AND VERSATILITY. GOURMET KITCHEN::::::::: THE HEART OF THE HOME, THE GOURMET KITCHEN, IS A CHEF'S DELIGHT, FEATURING TOP-OF-THE-LINE STAINLESS STEEL APPLIANCES, QUARTZ COUNTERTOPS, CUSTOM CABINETRY, AND A GENEROUS CENTER ISLAND WITH BREAKFAST BAR SEATING. PERFECT FOR CULINARY CREATIONS AND HOSTING GATHERINGS WITH FRIENDS AND FAMILY. LUXURIOUS BEDROOMS AND BATHROOMS:::::::::: RETREAT TO THE TRANQUILITY OF THE MASTER SUITE, COMPLETE WITH A LAVISH ENSUITE BATHROOM FEATURING DUAL VANITIES, A LUXURIOUS SOAKING TUB, AND A SEPARATE GLASS-ENCLOSED SHOWER. ADDITIONAL BEDROOMS ARE GENEROUSLY SIZED AND OFFER AMPLE CLOSET SPACE, PROVIDING COMFORT AND PRIVACY FOR ALL. FOR SHOWINGS, AGENTS, AND NON-PARTICIPATING AGENTS PLEASE CALL THE COMPANY SHOWINGTIME REAL ESTATE APPOINTMENT CENTER BY LOOKING UP THE PHONE NUMBER ONLINE ON GOOGLE AND GIVE THE ADDRESS.
MlsNumber: --
ListingId: 11364372


Listing Provided By: LOMBARDO HOMES & ESTATES, original listing
Phone: (855) 919-0222
Office Phone: (516) 200-6448
Agent Name: Joseph Lombardo
Disclaimer: Copyright © 2024 My State 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