Combo Box Yes/No error

[nybaseball22]

Hello. I have a form that I want to add a function in the footer that
will allow me to sort the records based on whether they are active or
inactive. The field in my table is a yes/no check box that is check
for yes and unchecked for no.

I have tried to use a combo box with 2 columns, Active and Inactive
with corresponding fields yes/no check boxes. I set it up as a 2
column count combo box and hid the yes/no column so active/inactive
would appear. I get an error that the expression is too complex....

The information is passed to my query to the Active Yes/No column by
using:

[Forms]![ExpensesCode]![StatusCombo]

Any idea how I can make this work? I want to avoid adding another
query, so I am trying to pass the selection to this query.

Thanks

 

[croy]

Hello. I have a form that I want to add a function in the footer that
will allow me to sort the records based on whether they are active or
inactive. The field in my table is a yes/no check box that is check
for yes and unchecked for no.

I have tried to use a combo box with 2 columns, Active and Inactive
with corresponding fields yes/no check boxes. I set it up as a 2
column count combo box and hid the yes/no column so active/inactive
would appear. I get an error that the expression is too complex....

The information is passed to my query to the Active Yes/No column by
using:

[Forms]![ExpensesCode]![StatusCombo]

Any idea how I can make this work? I want to avoid adding another
query, so I am trying to pass the selection to this query.

Have you seen: http://allenbrowne.com/NoYesNo.html

?

 

[BruceM]

How is [Forms]![ExpensesCode]![StatusCombo] passed to the query?

One general approach is something like this in the combo box After Update
event:
Dim strOrder as String, strSQL as String

If Me.YourComboBox = "Active" Then
strOrder = ""
Else
strOrder = " DESC"
End If

strSQL = "SELECT * from YourQuery ORDER BY [Active]" & strOrder

Me.RecordSource = strSQL

This assumes the combo box has just two choices.