Hi & welcome to Bytes.com.
This is a perfect example of why it really doesn't make good sense to post SQL as VBA (See
How to Debug SQL String). As you're a new visitor I'll check the VBA for you and tell you that the code wouldn't even compile as it doesn't fit VBA syntax.
NB. You should
Always compile code before asking for help with it.
After your 4th hash (#) you end the string literal and then the text
And
is there as VBA syntax, followed by another string completely out of context.
If you remove the two quotes around your
And
then you'll at least have compilable code and a SQL string. "[BHUD] = 'YES' " as a string is fine. As SQL on the other hand, this will only work if [BHUD] is a text field that contains the text "YES". I suspect [BHUD] is actually a Boolean field so you could replace that with "[BHUD]=True" if you want to be messy, Alternatively just "[BHUD]" makes better sense.
PS. I fully support CactusData's comment about formatting date literals when used within SQL of any form. See
Literal DateTimes and Their Delimiters (#) for more on that.