sort report based on checkbox

Question

Hi,

I want to sort a report where any record that has a checkbox that is not checked would appear first.

I have this code in the "order by" property:

Service_Completed, Service_Date

where Service_Completed is a checkbox field.

My report comes out with the records that are checked are first.  I want the non checked records to be first.

How do I change my order by property.  I tried "Not(Service_Completed)" and other things, but nothing worked.

Your help would be appreciated.

Thanks,

Phil

Answer 1

Hi

Sorry, it is a form that I'm talking about. 

If I set the form open event to:

Abs([Service_Completed])

the form does not sort.

If I set the form open event to:

Service_Completed

The form sorts but with the checked boxes first.

I don't know why the Abs function will not work but I want the unchecked box records to appear first..

Any ideas?

Thanks again,

Phil

Answer 2

In your first post you refer to a report, but in the last post to a form.  The method of sorting differs in each.  With a form you can either order the form's underlying query with:

ORDER BY Abs([Service_Completed])

or you can set the form's OrderBy property to:

Abs([Service_Completed])

and in the form's Open event procedure put:

Me.OrderbyOn = True

In a report you should use the report's internal Sorting and Grouping mechanism and create a group level on the expression Abs([Service_Completed]).

This works because Access implements a Boolean TRUE as -1 and a FALSE as 0, so returning the absolute value with the Abs function allows the FALSE values to sort first as 0 sorts before 1.  If you regard this, as the head of one software company of my acquaintance calls it, as 'being unduly chummy with the implementation' you can use one of the following expressions:

IIf([Service_Completed],1,0)

or:

Not [Service_Completed]

or:

([Selected]=False)

instead of calling the Abs function.

Answer 3

I was speaking of the query underlying your form.

Answer 4

HI,

Thanks for your response.  I using the "Order by" property of the form and I enter  "Not Service_Completed", save, view the form and return to properties and my order by is changed  to "[Not Service_Completed]" and the form is not sort by the Service_Completed field.

Service_Completed is a checkbox field.

Thanks,

Phil

Answer 5

The parentheses are not needed:

order by Not Service_Completed, Service_Date

I am assuming Service_Completed is a Yes/No field.