Sorting for a Report Using Checkboxes

[darwal]

I'm fairly new to Access2002 and I'm trying to put together a database for a small car-repairs business. I have a main form/table containing pertinent data that includes Repair Type (Cats. - Engine, Transmission, Suspension, etc.) from a combo box. What I would like to do is be able to create a customized report that lists all repairs based on Repair Type that the user selects in another form using check boxes. I have a form/table that contains the categories with linked checkboxes, but I'm having trouble translating the "Yes" selections into a query for output to a report. Sorry I'm so ignorant about this but the "Northwind" thing is a little hard to follow. Any help greatly appreciated.

Greg Hendricks, MD
Huntington WV

 

[WayneRyan]

Greg,

You are going to have to build the SQL for the report's query
using VBA code. Actually, you are just constructing the ORDER
BY part.

Use the Search Facility here and look for "QueryDefs" ... That's
what you need.

strSQL = "Select FieldA, FieldB, FieldC " & _
         "From   YourTable(s) "

If Me.CheckBox1 Then
   strSQL = strSQL & "Order by FieldA"
ElseIf Me.CheckBox2 Then
   strSQL = strSQL & "Order by FieldB"
ElseIf Me.CheckBox3 Then
   strSQL = strSQL & "Order by FieldC"
End If

Simplistic, but it's a start.

Wayne

 

[darwal]

Thanks for the help, Wayne. I haven't had a chance to fool around with the code yet, but I do appreciate you taking the time to help!

Greg

 

[dching]

Greg,

You can also put an "if" statement in your query to translate the "yes" to a catagory item.

For example:

If you have a check box field for Engine and a check box field for Transmission, then, in the query you would create 2 selections of:

CatEngine: iif([Engine]=-1,"Engine","")
CatTransmission: iif([Transmission]=-1,"Transmission","")

The "" is the same as producing a blank in the query. Also note that "yes" in a check box is "-1" and "no" in a check box is "0".

Derek

 

[darwal]

Derek, thanks for the post. Where would the "if" statement need to be located, and does it matter that the table linked to the checkbox form is not the same table that contains the actual repair data?