query on two multi-select boxes

I have one multiselect box called 'listclient.' I have another
multi-select box called 'listemployee.' I found some code that allows
me to query on the listclient box. I'm trying to figure out how to get
my query to query on the listemployee box as well. Thanks in advance
for any help.

Here's my code for querying on the listclient box:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT
datetest1_tbl.fld_year,datetest1_tbl.fld_day,datet est1_tbl.fld_month,datetest1_tbl.fld_break_mins,da tetest1_tbl.fld_break_hrs,datetest1_tbl.fld_date,d atetest1_tbl.fld_client,datetest1_tbl.fld_project, datetest1_tbl.fld_subproject,datetest1_tbl.fld_cur rency,
datetest1_tbl.fld_duration_hrs,datetest1_tbl.fld_d uration_mins,
datetest1_tbl.fld_note, datetest1_tbl.fld_rate,
datetest1_tbl.fld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl.fld_date) Between
Forms!aspdash_form!date1 And Forms!aspdash_form!date2) and
datetest1_tbl.fld_client IN ("
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" & listclient.Column(0, i) & "', "
End If

Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL


'*** delete the previous query
db.QueryDefs.delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

'*** open the query
'*** DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is
missing
Resume Next '*** then skip the delete line and
resume on the next line
Else
MsgBox Err.Description '*** write out the error and exit
the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

re: query on two multi-select boxes

"gambit32" <somethings.amiss@gmail.comwrote in
news:1155339759.822338.307240@i3g2000cwc.googlegro ups.com:
Telling you how to modify the code wouldn't be helping you. You
have to learn a little bit about what the code does. Take a few
minutes to read the code and figure out what the existing code
does. Highlight any keyword, press F1, and Access will give you
some information.

Once you understand the code, the answer to your question will
be so obvious that you will be saying Doh!!!

Hint: look for the name of your Client listbox, you would handle
the employee listbox the same way.

_tbl.fld__tbl.fld_break_hrs,_tbl.f_tbl.fld_curre_tbl.fld_duration_mins,

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

re: query on two multi-select boxes

I understand the code. I was just curious where to insert the code for
the other list box. I'll give it a go today and post my results.

Thanks!
Bob Quintal wrote:

re: query on two multi-select boxes

Should I be using strWhere2? Am I on the right track?
gambit32 wrote:

re: query on two multi-select boxes

"gambit32" <somethings.amiss@gmail.comwrote in
news:1155575843.916790.202710@h48g2000cwc.googlegr oups.com:
If you understood the code, you would not need to ask where to
put the second listbox code.

Existing code:

For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" _
& listclient.Column(0, i) _
& "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

'New code to put right after.:
strWhere = strWhere & " AND datetest1_tbl.fld_employee IN ("
For i = 0 To listemployee.ListCount - 1
If listemployee.Selected(i) Then
strWhere = strWhere & "'" _
& listemployee.Column(0, i) _
& "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"





--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com