I keep getting a syntax error on the last part of this statement:
"SELECT DISTINCT [Company Name], [Street Address] FROM " & d & " WHERE
[Street Address] !="
Maybe I am using the wrong syntax, but how do I also get distinct addresses when this database has duplicate names and addresses, and some with duplicate names and different addresses?
The syntax error in this SQL statement is most likely the missing "!=" condition. You must specify what the [Street Address] column is not equal to. If I understand your situation, you could have duplicate address and duplicate names so you could create a subquery that first gets distinct addresses and then gets distinct names from that subset of records like this:
SELECT DISTINCT [Company Name]
WHERE [Street Address] IN
(SELECT DISTINCT [Street Address] FROM TEST)