Square brackets don't delimit strings, they delimit names that contain illegal characters (like spaces)
So if you are trying to compare a column with a string, you need to replace the "[" and "]" characters around it with quotes:
'What is the Street Address?'
You also need to remove the spurious trailing comma in the SELECT list:
SELECT tbl_HouseholdApplication.NameLast,tbl_HouseholdApplication.Address,
^
|
WHERE (((tbl_HouseholdApplication.Address)=[What is the Street Address?]))
ORDER BY tbl_HouseholdApplication.NameLast, tbl_HouseholdApplication.Address;
SQL doesn't like them!
It may be ugly, but it's better to use leading commas rather than trailing.