Need help formatting an Access report from a query and SQL help
December 20, 2010 6:58 AM Subscribe
I'm having trouble formatting a report in Access that ONLY shows checkboxes that are not checked. I probably need some help on the SQL front.
posted by desjardins to computers & internet (4 answers total) 1 user marked this as a favorite
My database is based around a list of tasks. When the person finishes a task, they check it off. I want a report of ONLY the tasks which are incomplete, so I have a query along the lines of this:
SELECT Projects.ProjectID, Projects.ProjectName, Projects.Check01, Projects.Check02, Projects.Check03
WHERE (((Projects.Check01)=No)) OR (((Projects.Check01)=No)) OR (((Projects.Check01)=No));
This gives me a list of projects for at least one task is not complete. For example, for project "Grocery Shopping," I clipped the coupons but did not make a list or go to the store.
All I want to show on the report is that I still need to make a list and go to the store. So in my report code, I have
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Check01 = False Then
Check01.Visible = True
Check01.Visible = False
If Check02 = False Then
Check02.Visible = True
Check02.Visible = False
If Check03 = False Then
Check03.Visible = True
Check03.Visible = False
This gives me a blank line where "clipping coupons" would be, because I've finished that task. Is there a way to eliminate the blank lines? I have 25 tasks per project (this is stable, and all of the tasks are the same for every project) so if half of them are finished, the page looks horrible. I would be fine with almost any kind of list (text file, whatever) as long as it ONLY shows the incomplete tasks in some sort of coherent form. I'm using Access 2002 if it matters, but I'll probably be getting 2007 or 2010 soon.