Row Source - combo box, filtering what pops up in a combo box by date

Question

I have a combo box that I want to pop up when somebody wants to search by year. It will allow them to see in that combo box only results that happened in a certain year.

So far I have something like

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE (((Database_New.Date) >= DateValue('01/01/2001') 
AND  (((Database_New.Date) <= DateValue('12/031/2001')));

or

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE (((Database_New.Date) >= BETWEEN DateValue('01/01/2001') 
AND  DateValue('12/31/2001’)));

As you can see, the kicker is that I am already sorting the thing with SELECT DISTINCT under the ASEC field. However, I want to filter it one more by year so a whole bunch of ASEC values that didn't happen in that year (and there are quite a few that happen rarely, or would only be associated with one year) do not pop up as available.

So far I get this error

"Syntax error in query expression '(((Database_New.Date) >= DateValue('01/01/2001') AND (((Database_New.Date) <= DateValue('12/031/2001')))'

and I am a VBA person, not quite as good at debugging SQL.

Is it something easy or will it simply not work the way I have it set up?

Answer 1

The syntax error comes from imbalanced parentheses. Just don't use any parens at all, you don't need them in an expression as simple as this one:

Database_New.Date >= DateValue('01/01/2001') 
AND Database_New.Date <= DateValue('12/031/2001')