MS Acess SQL - where clause to get year from date based on the year - data located in MS access form

Question

OK, back again. I have a problem getting a drop down list to populate based on information in two fields.

I have got the SQL correct as to Select just one year if I put DateValue('01/01/2001') in both places, but I am trying now to get it to grab the year data from the MS access form - another drop down named "cboYear".

I'd hate to have to do something in VB, unless necessary.

so far I have gotten this to pull up something (its always incorrect)

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Database_New.Date>=DateValue('01/01/' & [cboYear]) 
      And Database_New.Date<=DateValue('12/31/' & [cboYear]);

and

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Database_New.Date>=DateValue('01/01/' + [cboYear]) 
      And Database_New.Date<=DateValue('12/31/' + [cboYear]);


SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Database_New.Date>=DateValue('01/01/' AND [cboYear]) 
     And Database_New.Date<=DateValue('12/31/' AND [cboYear]);

both give errors saying that it is too complex to compute.

Its probably something simple, but where do I go from here?

edit/addition

completely blanked out - yeah sorry about that

cboYear is not linked to anything, it just displays the distinct years avalible in the database, Ex - 2001, 2002, 2003 - just 4 digits.

Answer 1

What exactly is the value of [cboYear]? As in, does the ComboBox Value property actually contain four digit numeric values?

Does this work?

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Year(Database_New.Date) = [cboYear];

Answer 2

Another solution you might try is:

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Database_New.Date>= DateSerial([cboYear], 1, 1) 
      And Database_New.Date<= DateSerial([cboYear], 12, 31);

Answer 3

Say you have a list box named lstASEC on a from named frmFoo. The RowSource for lstASEC is a query based on the value of a combo box named cboYear.

So I think the RowSource SQL should explicitly identify where to find cboYear.

SELECT DISTINCT Database_New.ASEC
FROM Database_New
WHERE Year(Database_New.[Date]) = Forms!frmFoo!cboYear;

In the after update event of cboYear, requery lstASEC so its content is updated to match the selected year.

Me.lstASEC.Requery

I still think you should change the field's name from Date. In this case I enclosed Date with square brackets to make it unambiguous for Access to recognize it as a field rather than the Date() function.