Building SQL from Filter Form (1 Viewer)

[tacieslik]

I have attached a database that contains a problem I've been tring to solve for some time.

I have a table (tblInventory) and a form (frmFilter). The frmFilter has 6 check boxes and 7 combos. When the check boxes are true, the combos are enabled and these are bound to the relevant fields within my tblInventory. Basically the user will decide which items he will want to filter by and then choose the criteria from the combos. There are 7 combos because one of them is a second 'Stored Time' combo for specifiying a date range.

What I want to achive is when the View Query button is pressed, a Definition Query be created and the relevant SQL string be built from the criteria given. So if a check box for an item is False, it will not be part of the query.

I don't know if this is going to be the best way of doing this and I'm a bit limited on knowledge to progress much further so I would appritiate any help and advice you guys can give me.

TIA

 

[Jon K]

I have changed the Row Source propertiy of the combo boxes and added a query qryFiltered:-

SELECT tblInventory.*
FROM tblInventory
WHERE IIf([Forms]![frmFilter]!chkCrane,Crane=[Forms]![frmFilter]!cmbCrane,True) And
IIf([Forms]![frmFilter]!chkCol,Col=[Forms]![frmFilter]!cmbCol,True) And
IIf([Forms]![frmFilter]!chkRow,Row=[Forms]![frmFilter]!cmbRow,True) And
IIf([Forms]![frmFilter]!chkSide,Side=[Forms]![frmFilter]!cmbSide,True) And
IIf([Forms]![frmFilter]!chkCellStatus,CellStatus=[Forms]![frmFilter]!cmbCellStatus,True) And
IIf([Forms]![frmFilter]!chkStored,STime Between [Forms]![frmFilter]!cmbStored1 And [Forms]![frmFilter]!cmbStored2,True);


Note  In each of the IIF functions, if the corresponding checkbox is not checked, the word True will return every record.


If you want to build the SQL string whenever the button is pressed, there is an example that you can download from this KB article (Qrysmp00.exe).
http://support.microsoft.com/default.aspx?scid=kb;en-us;210242&Product=acc2000

 

[tacieslik]

Thankyou very much Jon,

This is exactly what I wanted to do. I'm a bit new to using SQL as I've always been able to do without it.

Many Thanks again.
TAC