query on two multi-select boxes

[gambit32]

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

[Bob Quintal]

"gambit32" <so**************@gmail.comwrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

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.

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.

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,datetest1

_tbl.fld_break_hrs,

datetest1_tbl.fld_date,datetest1_tbl.fld_client,da tetest1

_tbl.f

ld_project,datetest1_tbl.fld_subproject,datetest1

_tbl.fld_curre

ncy,
datetest1_tbl.fld_duration_hrs,datetest1

_tbl.fld_duration_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

--
Bob Quintal

PA is y I've altered my email address.

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

[gambit32]

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:

"gambit32" <so**************@gmail.comwrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

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.

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.

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,datetest1

_tbl.fld_break_hrs,

datetest1_tbl.fld_date,datetest1_tbl.fld_client,da tetest1

_tbl.f

ld_project,datetest1_tbl.fld_subproject,datetest1

_tbl.fld_curre

ncy,
datetest1_tbl.fld_duration_hrs,datetest1

_tbl.fld_duration_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

--
Bob Quintal

PA is y I've altered my email address.

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

[gambit32]

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

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:

"gambit32" <so**************@gmail.comwrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

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.
>

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.

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,datetest1

_tbl.fld_break_hrs,

datetest1_tbl.fld_date,datetest1_tbl.fld_client,da tetest1

_tbl.f

ld_project,datetest1_tbl.fld_subproject,datetest1

_tbl.fld_curre

ncy,
datetest1_tbl.fld_duration_hrs,datetest1

_tbl.fld_duration_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
>
>

--
Bob Quintal

PA is y I've altered my email address.

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

[Bob Quintal]

"gambit32" <so**************@gmail.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

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.

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) & ")"

>
Thanks!
Bob Quintal wrote:

>"gambit32" <so**************@gmail.comwrote in
news:11**********************@i3g2000cwc.googlegr oups.com:

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.

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.

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,datetest1

_tbl.fld_break_hrs,

datetest1_tbl.fld_date,datetest1_tbl.fld_client,da tetest1

_tbl.f

ld_project,datetest1_tbl.fld_subproject,datetest1

_tbl.fld_curre

ncy,
datetest1_tbl.fld_duration_hrs,datetest1

_tbl.fld_duration_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

--
Bob Quintal

PA is y I've altered my email address.

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

--
Bob Quintal

PA is y I've altered my email address.

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