SQL Statement Multiple Criteria - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL Statement Multiple Criteria

Walter Hamilton

4 years ago

I have a form with multiple combo boxes that are used to filter a subform.  Four of the five combo boxes allow the user to drill down on the Assets located at:

-Company
-Site
-Room
-Location

The fifth combo box is Status.

I want each combo box to take into account what is selected in itself as well as the Status and vice versa.

I have everything working is something is selected in each combo box, but I need to include the ability to filter off all the combo boxes that have something selected and ignore any combo box that is null.

My current SQL Statement is:

Dim SQL As String

    SQL = "SELECT tbl_Make.Make, tbl_ProductLine.ProductLine, tbl_Model.Model, tbl_Item.AssetNumber, tbl_Item.SerialNumber, " _
    & "tbl_Item.PurchaseDate, tbl_Item.Cost, tbl_Item.Fund_ID, tbl_Item.Item_ID, tbl_Item.Company_ID, tbl_Item.Site_ID, " _
    & "tbl_Item.Room_ID, tbl_Item.Location_ID, tbl_Item.Status_ID " _
    & "FROM tbl_Make RIGHT JOIN ((tbl_ProductLine RIGHT JOIN tbl_Model ON tbl_ProductLine.Product_ID = tbl_Model.Product_ID) " _
    & "RIGHT JOIN tbl_Item ON tbl_Model.Model_ID = tbl_Item.Model_ID) ON tbl_Make.Make_ID = tbl_ProductLine.Make_ID " _
    & "WHERE tbl_Item.Status_ID=" & Me.Status_ID & " " _
    & "AND tbl_Item.Company_ID=" & Me.Company_ID & " " _
    & "AND tbl_Item.Site_ID=" & Me.Site_ID & " " _
    & "AND tbl_Item.Room_ID=" & Me.Room_ID & " " _
    & "AND tbl_Item.Location_ID=" & Me.Location_ID & " " _
    & "ORDER BY tbl_Item.AssetNumber;"

Me.frm_Item_List.Form.RecordSource = SQL
Me.frm_Item_List.Form.Requery

Kevin Yip @Reply

4 years ago

You can construct your WHERE for one combo box at a time, and only for those that aren't null. First, construct the SQL statement up to the first criteria for Status, which I assume is never null:

sql = "SELECT.....WHERE tbl_Item.Status_ID=" & Me.Status_ID & " "

Then you check each combo box and add additional criteria to the sql accordingly:

If Not Isnull(Me.Company_ID) Then sql =sql & "AND tbl_Item.Company_ID=" & Me.Company_ID & " "
If Not Isnull(Me.Site_ID) Then sql =sql & "AND tbl_Item.Site_ID=" & Me.Site_ID & " "

And so forth.

Finally,

sql = sql & "ORDER BY tbl_Item.AssetNumber;"

So this is basically a string concatenation problem.

	Scott Axton @Reply 4 years ago
Check out the Search Form 2.0 . That will guide you on building your search string / where string.

	Walter Hamilton OP @Reply 4 years ago
Kevin and Scott - Thank you. I was able to get everything working after watching the video and playing around with the SQP statement. I had to pull the ORDER BY to the end of everything. I'll post the final VBA in the next response.

	Walter Hamilton OP @Reply 4 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View