Ok guys, this is probably a simple

[JasonPurdueEE]

Ok guys, this is probably a simple question for those of you who are more experienced with writing queries, but I'm stuck. I'm building a series of multi-select listboxes that come up with the criteria for the WHERE part of a query and pass the argument onto the querydef. All this is going fine, but I need a little help with writing the query. Here's what I need: the user can select none, one or many items from a single list box. there are 4 listboxes. I want the query to return records that match ALL the critera selected in ALL 4 listboxes. as it is now, it returns all the records that match the criteria for listbox1, listbox2...

Here's my SQL statement:

SELECT TblWrenchTimeLog.Date, TblWrenchTimeLog.WTL_ID, ComboBoxUnitID.Unit, Tbl_CrewID.Crew, TblWrenchTimeImpacts.WorkOrder, TblWrenchTimeCode.WTI_Code_Descr, TblWrenchTimeImpacts.[#ofEmployees], TblWrenchTimeImpacts.WTI_Mins, [TblWrenchTimeImpacts]![#ofEmployees]*[TblWrenchTimeImpacts]![WTI_Mins] AS TotWTIMins, TblWrenchTimeImpacts.Comment, TblWrenchTimeLog.TotalREGHours

FROM ((Tbl_CrewID INNER JOIN TblWrenchTimeLog ON Tbl_CrewID.Crew_ID = TblWrenchTimeLog.Crew_id) INNER JOIN (TblWrenchTimeCode INNER JOIN TblWrenchTimeImpacts ON TblWrenchTimeCode.WTI_Code = TblWrenchTimeImpacts.WTI_code) ON TblWrenchTimeLog.WTL_ID = TblWrenchTimeImpacts.WTL_id) INNER JOIN ComboBoxUnitID ON TblWrenchTimeLog.Unit_id = ComboBoxUnitID.UnitID

WHERE (((ComboBoxUnitID.Unit)='GC1')) OR (((ComboBoxUnitID.Unit)='GC2') AND ((Tbl_CrewID.Crew)='AEIT')) OR (((Tbl_CrewID.Crew)='MECH')) OR (((Tbl_CrewID.Crew)='PIPE')) OR (((Tbl_CrewID.Crew)='SCAF') AND ((TblWrenchTimeImpacts.WorkOrder)='33322401-01')) OR (((TblWrenchTimeImpacts.WorkOrder)='33322403-01')) OR (((TblWrenchTimeLog.Date) Between #6/6/2002# And #6/6/2004#) AND ((TblWrenchTimeImpacts.WorkOrder)='33325585-01') AND ((TblWrenchTimeCode.WTI_Code_Descr)='Breaks'));

any ideas on how to accomplish this? if clarification is needed, please let me know. many thanks in advance.

JASON

 

[JasonPurdueEE]

heres a better example of the WHERE statement that is passed by VBA into the querdef.

WHERE [Unit] = 'BOC' Or [Unit] = 'CCP' Or [Unit] = 'CGF' AND [Crew] = 'AEIT' Or [Crew] = 'CRN' Or [Crew] = 'DSL' AND [WorkOrder] = '32322484-01' Or [WorkOrder] = '32343604-02' Or [WorkOrder] = '33300326-02' AND [WTI_Code_Descr] = 'Admininstrative' Or [WTI_Code_Descr] = 'Breaks' Or [WTI_Code_Descr] = 'Changeout' AND Date Between #6/6/2002# AND #6/6/2004#

this might be a little easier to decypher (its better grouped) and shows exactly what is being passed into the querydef.

JASON

 

[JasonPurdueEE]

am I going to have to write subqueries to do this? for example, look for matching records for Unit then to search for Crews matching that Unit I could make a subquery in the Crew criteria. any ideas?

JASON

 

[scriverb]

Jason, you have quite a query here. Check out this thread: thread701-473531 I had previously helped with a project similar and used the Instr function to perform the compares that you need in your query. Follow my posts to the end because they it evolves a little. It does require a Function to be created but works quite well.

I don't think you need to perform subqueries here. The key is selecting the correct records from the joined tables where there can be X number of selections in the listboxes. The above thread will help you with that.

Get back with me if you have questions and need additional help.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Jason, here the Function that needs to be created for your situation. Copy and paste into a database module:

Public Function CompareStr(ctlList As Control)
Dim varItem As Variant, strCollect As String
strCollect = Empty
'Enumerate through selected items only.
For Each varItem In ctlList.ItemsSelected
'Create a string of values of listbox selection each
'having a comma before and after
If strCollect = Empty Then
strCollect = "," & CStr(ctlList.ItemData(varItem))
Else: strCollect = strCollect & "," & CStr(ctlList.ItemData(varItem))
End If
Next varItem
CompareStr = strCollect & ","
End Function

To call this function the following format should be used:
CompareStr(Forms![frmYourFormName]![lstYourListBoxName])
Just update the red form and listbox control names to the ones on your form and a compare string will be created of the single or multiple selections from that listbox.

In a WHERE statement this is how it would be used. Matching up the table.field with the function call analyzing the appropriate form listbox:
WHERE Instr(1, CompareStr(Forms![frmYourFormName]![lstYourListBoxName]) ,tblYourTableName.FieldName) > 0

By adding additional expressions for each table field comparing to another listbox with the OR operator you can achieve the results that you requested.

Why don't your look this over and see if it will work in your situation.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[JasonPurdueEE]

Thank you Bob, I will look over that thread and code later today (I have been swamped with other stuff this morning cause it is my last day of this rotation). I appreciate the help, I was pretty unsucessful in attempting to create subqueries on-the-fly yesterday, so I am glad there may be an alternative. Again, thanks.

JASON

 

[scriverb]

Okay, just post back with questions as I am sure this will work for you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???