One combo generating rowsource for second combo

[Guest]

Access 2003.

I have two comboboxes on a form used to generate report criteria.
Combo #1 is for the year. Combo #2 has two columns, one for
the year and one for the quarter (1, 2, 3, or 4).

When the form opens, the rowsource for the second combo (year/qtr)
is every year and the quarters in that year with data for that year:

strSQL = "SELECT DISTINCT DATEPART(yyyy, GoalDate) AS Year, " & _
"DATEPART(q, GoalDate) AS Quarter " & _
"FROM tblChildGoals " & _
"ORDER BY year"


Me.cboQuarter.RowSource = strSQL

Once the user select the year from the combo #1, the rowsource in combo #2
changes to only reflect years that match combo #1, plus their quarters.

Private Sub cboYear_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT DISTINCT DATEPART(yyyy, GoalDate) AS Year, " & _
"DATEPART(q, GoalDate) AS Quarter " & _
"FROM tblChildGoals " & _
"WHERE DATEPART(yyyy, GoalDate) = '" & Me.txtYear
& "'" & _
" ORDER BY year"

Me.cboQuarter.RowSource = strSQL


End Sub
*********************************************************
What's not working.....once the user selects combo #2 with year/quarter, the
combo box fills with the year, instead of the quarter, which is column 2 in
this combo box. I have tried changing the bound column, but cannot think of
what it might be. Can anyone please help me??? I'd much appreciate it.
Thanks in advance.

 

[Douglas J. Steele]

DatePart returns a number, not text. Remove the quotes you've got around
Me.txtYear in your query.

Also, don't use Year and Quarter for your field names: Year is definitely a
reserved word, and I think Quarter is as well.