473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

oh my goodness...multiple criteria

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
4 1569
Rabbit
12,516 Expert Mod 8TB
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
1,356 Expert 1GB
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
leeg
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
leeg
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

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

Similar topics

1
by: TH | last post by:
I am (still :) working on a recipe database. Now I am trying to figure out how to set it up for an ingredient search. What I want it to be able to do is three things: 1. Search based on just...
4
by: ShyGuy | last post by:
I have a table with 4 fields. Three are used for criteria. I can get the DLookup to work with 1 criteria with the following but can't get it to work with 2 or three. NumofAppts = DLookup("",...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
4
by: carl.barrett | last post by:
Hi, I have a list box that displays 2 columns. Behind it sits a query with five columns. These are Column1 (DOB), column2 (a concatenated string of Surname Forname, Title), Column3 (Surname),...
6
by: NOSPAMrclark | last post by:
I'm wondering . . . . What is the generally accepted method for calling reports from multiple locations? Example: REPORT-A is called by selecting a record in a listbox and clicking a...
0
by: Greg Strong | last post by:
Hello All, In the past I've used a combo box with the 'row source' being an Access SQL union query to select "All" or 1 for only 1 criteria in a query. An example is as follows: SELECT 0 As...
7
by: Ceebaby via AccessMonster.com | last post by:
Hi All Here's hoping someone can help me with this. I have a report based on a query where the criteria for 4 of the fields is set from an unbound form. I want the user to be able to select any...
2
by: dlevene | last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from...
4
by: jvan2008 | last post by:
"Form1" combobox "cboModel" Row Source SELECT ., . FROM tblModel ORDER BY ; combobox "cboContactName" SELECT . FROM Query1 ORDER BY ;
3
by: kstevens | last post by:
Please help. I know the sysntax is wrong. Here are some details. I am looking for the sum of past shipped items. I originally wrote this Dsum with only one criteria, but because of multiple...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.