combo box problem

[Johnny Chow]

Hi


any one can help me, I run sql statement one, there is no respond, second and third sql statement, there is an error 2580 but I run the sql statement four, there is no problem, I want to use the value of the combo box to get what I want. please help. Thanks




Private Sub Combo7_AfterUpdate()


Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim yy As Date


yy = Me.Combo7


Set conn = CurrentProject.Connection


'sql = "select [Catagoey],[Disbursements],[Sum of Total]from [Disbursements Sum Query] where [Date By Year] = #" & yy & "# "
'sql = "select [Catagoey],[Disbursements],[Sum of Total]from [Disbursements Sum Query] where [Date By Year] = Me.Combo7.Value"
sql = "select [Catagoey],[Disbursements],[Sum of Total]from [Disbursements Sum Query] where [Date By Year]" = Me.Combo7.Value
sql = "select [Catagoey],[Disbursements],[Sum of Total]from [Disbursements Sum Query] where [Date By Year] = 2018"


Me.RecordSource = sql


Set rst = Nothing


End Sub

[moke123]

First thing you should do when trying to debug an Sql statement is to add a debug.Print after the construct. That way you can see what it resolves to.
You also need to place the variable outside the sql statement and delimit it as needed.
What column in the combobox is DateByYear? Whats the datatype?

if it is text it should look something like below

sql = "select [Catagoey],[Disbursements],[Sum of Total]from [Disbursements Sum Query] where [Date By Year]  =  '" & Me.Combo7.column(?)  & "'"

Debug.Print sql

You should eliminate the spaces in your field and object names.
Not sure what the ADO references are for so i wont comment on that.

[June7]

Year alone is not a date value, it is only a number. The = sign must be within quote marks and then concatenate reference to combobox.

sql = "select [Catagoey],[Disbursements],[Sum of Total] from [Disbursements Sum Query] where [Date By Year]=" & Me.Combo7

I am guessing Catagoey is really intended to be Category. Might consider correcting the field name in table.

Also see no reason for the recordset code and agree about no spaces in naming convention - nor punctuation/special characters (underscore only exception).

Instead of setting RecordSource, could set Filter and FilterOn properties.

[orange]

Lots of combobox info here.

Good luck.

[Johnny Chow]

I have solved the problem because your help, thanks very much!