I’m gonna use generic names for the objects to show you how this technique works, replace “table1” for the name of your table and change the field names etc. to reflect your current DB.
Create a table called “table1” with the following fields:
Name(text)
Price(currency)
Gross(currency)
IM(currency)
Create a query called “Result”. Set the SQL for the query to “select * from table1”
Create a form with the following objects added:
1 Listbox called “lstResult” make sure the column count is set to 4. You can increase this if your select query returns more than 4 coloumns, which it will going by what you said, but for this example just set it to 4.
3 Textboxes called “txtPrice”, “txtGross”, “txtIM”
3 Combo boxes called “cboPrice”,”cboGross”,”cboIM”
Set the row source type of each combo box to value list.
In the row source type: >;<;=
Add labels; see my pic below for what I mean.
Add a button that will search the records.
Right click on button and left click on build event, and select code builder. Type the following code(not the most eloquent but it works!):
Dim db As Database
Dim q As QueryDef
Dim sql As String
Dim sqlbuild As String
Set db = CurrentDb
Set q = db.QueryDefs("Result")
sqlbuild = ""
sql = "Select * from table1"
'Build relevant sql statement to add to the standard select statement
If Chkim = True Then
sqlbuild = " WHERE im " & cboim & " " & txtim
End If
If chkgross = True Then
If sqlbuild <> "" Then
sqlbuild = sqlbuild & " AND " & "gross " & cbogross & " " & txtgross
Else
sqlbuild = "WHERE gross " & cbogross & " " & txtgross
End If
End If
If chkprice = True Then
If sqlbuild <> "" Then
sqlbuild = sqlbuild & " AND " & "price " & cboprice & " " & txtprice
Else
sqlbuild = "WHERE price " & cboprice & " " & txtprice
End If
End If
If sqlbuild <> "" Then
sql = sql & sqlbuild
End If
q.sql = sql
q.Close
‘Refresh form
Me.Refresh
Your form should look some thing like this.
The query will only add conditions to the select statement if the check box above the condition is ticked. There is no error handling as I only quickly knocked this up.
You may need to add a reference to DAO 2.6 so you can use the QueryDef method. If you don’t know how to do that let me know and also let me know what version of Access you’re running.
Good luck