By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

oh my goodness...multiple criteria

P: 14
Please help.

If I am to do what I think I need to do, i'll be here for years!!!

I have 8 fields which I need to give the user the option to search on all or some or any of the combo boxes. All the combo boxes are pre-populated obviously so data entegrity is not an issue.

I have googled and found that doing it in SQL is probably the best option, but mathematically, 8 x 8 x 8 permutations = 512 entries

How on earth do you start to do this. Surely there's a more efficient way to do it? Unfortunately, I'm new to access and programming AND SQL so it's a job and a half. Here's some code that I've started for 3 fields.

SELECT MainJobform.[Job Reference no], MainJobform.[Date of request], MainJobform.[Requested by], IsNull([forms]![fAdvancedSearch]![comboReqBy]) AS Expr1, MainJobform.Room, IsNull([forms]![fAdvancedSearch]![comboRoomName]) AS Expr2, MainJobform.Area, MainJobform.[Job category], MainJobform.[Priority rating], MainJobform.[Estimated time for job], MainJobform.[Estimated cost for job], MainJobform.[Description of request], MainJobform.[Job status], MainJobform.[Job allocated to], MainJobform.[Actual Time for job], MainJobform.[Actual cost for job], MainJobform.[Site Managers comments], MainJobform.[Date Completed], IIf(IsNull(MainJobform![Date Completed]),"N/A",DateDiff("d",MainJobform![Date of request],MainJobform![Date Completed])) AS [Days taken], IIf(IsNull(MainJobform![Date Completed]),DateDiff("d",MainJobform![Date of request],Now()),DateDiff("d",MainJobform![Date of request],MainJobform![Date Completed])) AS [Days since request], MainJobform.[Special Instructions], MainJobform.[Contractor type], MainJobform.[Contractors name], MainJobform.[Overall status]
FROM MainJobform
WHERE (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((MainJobform.Room)=[Forms]![fAdvancedSearch]![comboRoomName])) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])
OR (((IsNull([forms]![fAdvancedSearch]![comboReqBy]))<>False) AND ((MainJobform.Room)=[Forms]![fAdvancedSearch]![comboRoomName])) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])
OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((IsNull([forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat]) OR (((IsNull([forms]![fAdvancedSearch]![comboReqBy]))<>False) AND ((IsNull([forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat]) OR (((isnull([Forms]![fAdvancedSearch]![comboReqBy]))<>False) AND (((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False)) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat]))<>False))) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat]))<>False)) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND ((MainJobform.[Room])=[Forms]![fAdvancedSearch]![comboRoomName])) AND (((isnull([Forms]![fAdvancedSearch]![comboJobCat])<>False)))) OR (((MainJobform.[Requested by])=[Forms]![fAdvancedSearch]![comboReqBy]) AND (((isnull([Forms]![fAdvancedSearch]![comboRoomName]))<>False) AND ((MainJobform.[Job Category])=[Forms]![fAdvancedSearch]![comboJobCat])))

THanks in wild anticipation!!!

Graeme
Mar 16 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,364
I'm a little unsure of what you're looking for. Are you saying you have 8 fields that are bound to 8 combo boxes? And you need to search these comboboxes?

Or are you saying you need to search records in the table that match the 8 comboboxes?
Mar 16 '07 #2

Denburt
Expert 100+
P: 1,356
If I am on target I think I have recently dealt with a similar issue that got a lot more in depth and took a ride of it's own.

For searching records I usually use an unbound control in the forms header then using VBA filter the records. This allows me to choose the field or fields I want to search by then chose the records I want. The code provided allows me to use many (usually 6 fields) in the header and offers them 3 pieces of criteria. I have also designed a subform for this purpose that allows many more, I generally use that for reports and such.

I don't know how capable you are in any of these areas but I will be glad to try and help. If this is to complex maybe someone can show you a simpler way.

First create a query with the fields you want to include in any of your searchs then add a combo box to the header set the rowsourcetype to - Field List then add that query to the rowsource next you add a combo box to filter the records according to the field they selected.

I usually name the controls for this FilterBy0,FilterBy1,FilterBy2 etc.
then the searchby0,searchby1,searchby2.

The first combobox I use an afterupdate event like so
Expand|Select|Wrap|Line Numbers
  1. Private Sub FIRSTCOMBOBOX_AfterUpdate()
  2. If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
  3. MySearch Me, Me.ActiveControl, "SECONDCOMBOBOXNAME"
  4. End Sub
  5.  
  6. Public Function MySearch(frm As Form, FList As String, SrchBx As String)
  7. On Error GoTo Err_MySearch
  8. Dim QDef As QueryDef
  9. Dim myDB As Database
  10. Dim myField As Field
  11. Dim ctl As Control
  12. Set ctl = frm(SrchBx)
  13. Set myDB = CurrentDb
  14.  
  15. Set QDef = myDB.QueryDefs("YOURQUERYNAME")
  16. Set myField = QDef.Fields(FList)
  17. myField.SourceField
  18. frm(SrchBx) = ""
  19. frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
  20.  
  21. 'Me!Search0.RowSource = "Select " & myField.SourceField & " , Max(Distributions.RevVal) AS MaxOfRevVal From " & myField.SourceTable & " Group By " & myField.SourceField
  22. frm(SrchBx).Requery
  23.  
  24. Set myField = Nothing
  25. Set myDB = Nothing
  26. Set QDef = Nothing
  27. Exit sub
  28. Err_MySearch
  29. Msgbx err.number & "   " & err.description
  30. End Function
  31.  
The second combobox would filter the results. if you can get this far I will be glad to add the code I use for this also.
Mar 16 '07 #3

P: 14
I'm a little unsure of what you're looking for. Are you saying you have 8 fields that are bound to 8 combo boxes? And you need to search these comboboxes?

Or are you saying you need to search records in the table that match the 8 comboboxes?
Hi,

I have 8 fields in a table and the user needs to search/filter from an unbound form using combo boxes on all the 8 fields (using all 8 comboboxes) or none (giving all records) and also some of the 8 comboboxes, but not all.

Does that make sense?
Cheers, Graeme
Mar 19 '07 #4

P: 14
If I am on target I think I have recently dealt with a similar issue that got a lot more in depth and took a ride of it's own.

For searching records I usually use an unbound control in the forms header then using VBA filter the records. This allows me to choose the field or fields I want to search by then chose the records I want. The code provided allows me to use many (usually 6 fields) in the header and offers them 3 pieces of criteria. I have also designed a subform for this purpose that allows many more, I generally use that for reports and such.

I don't know how capable you are in any of these areas but I will be glad to try and help. If this is to complex maybe someone can show you a simpler way.

First create a query with the fields you want to include in any of your searchs then add a combo box to the header set the rowsourcetype to - Field List then add that query to the rowsource next you add a combo box to filter the records according to the field they selected.

I usually name the controls for this FilterBy0,FilterBy1,FilterBy2 etc.
then the searchby0,searchby1,searchby2.

The first combobox I use an afterupdate event like so
Expand|Select|Wrap|Line Numbers
  1. Private Sub FIRSTCOMBOBOX_AfterUpdate()
  2. If IsNull(Me.ActiveControl) Or Trim(Me.ActiveControl) = "" Then Exit Sub
  3. MySearch Me, Me.ActiveControl, "SECONDCOMBOBOXNAME"
  4. End Sub
  5.  
  6. Public Function MySearch(frm As Form, FList As String, SrchBx As String)
  7. On Error GoTo Err_MySearch
  8. Dim QDef As QueryDef
  9. Dim myDB As Database
  10. Dim myField As Field
  11. Dim ctl As Control
  12. Set ctl = frm(SrchBx)
  13. Set myDB = CurrentDb
  14.  
  15. Set QDef = myDB.QueryDefs("YOURQUERYNAME")
  16. Set myField = QDef.Fields(FList)
  17. myField.SourceField
  18. frm(SrchBx) = ""
  19. frm(SrchBx).RowSource = "Select " & myField.SourceField & " From " & myField.SourceTable & " Group By " & myField.SourceField
  20.  
  21. 'Me!Search0.RowSource = "Select " & myField.SourceField & " , Max(Distributions.RevVal) AS MaxOfRevVal From " & myField.SourceTable & " Group By " & myField.SourceField
  22. frm(SrchBx).Requery
  23.  
  24. Set myField = Nothing
  25. Set myDB = Nothing
  26. Set QDef = Nothing
  27. Exit sub
  28. Err_MySearch
  29. Msgbx err.number & "   " & err.description
  30. End Function
  31.  
The second combobox would filter the results. if you can get this far I will be glad to add the code I use for this also.
Thanks very much. I'll have a look at this today!
Mar 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.