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_Cli ck()
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.f ld_year,datetes t1_tbl.fld_day, datetest1_tbl.f ld_month,datete st1_tbl.fld_bre ak_mins,datetes t1_tbl.fld_brea k_hrs,datetest1 _tbl.fld_date,d atetest1_tbl.fl d_client,datete st1_tbl.fld_pro ject,datetest1_ tbl.fld_subproj ect,datetest1_t bl.fld_currency ,
datetest1_tbl.f ld_duration_hrs ,datetest1_tbl. fld_duration_mi ns,
datetest1_tbl.f ld_note, datetest1_tbl.f ld_rate,
datetest1_tbl.f ld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl .fld_date) Between
Forms!aspdash_f orm!date1 And Forms!aspdash_f orm!date2) and
datetest1_tbl.f ld_client IN ("
For i = 0 To listclient.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" & listclient.Colu mn(0, i) & "', "
End If

Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.de lete "qryMyQuery "
Set qdf = db.CreateQueryD ef("qryMyQuery" , strSQL)

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

Exit_cmdRunQuer y_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_cmdRunQuer y_Click
End If
End Sub

[Bob Quintal]

"gambit32" <so************ **@gmail.comwro te in
news:11******** **************@ i3g2000cwc.goog legroups.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_Cli ck()
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.f ld_year,datetes t1_tbl.fld_day, datetest1

_tbl.fld_

month,datetest1 _tbl.fld_break_ mins,datetest1

_tbl.fld_break_ hrs,

datetest1_tbl.f ld_date,datetes t1_tbl.fld_clie nt,datetest1

_tbl.f

ld_project,date test1_tbl.fld_s ubproject,datet est1

_tbl.fld_curre

ncy,
datetest1_tbl.f ld_duration_hrs ,datetest1

_tbl.fld_durati on_mins,

datetest1_tbl.f ld_note, datetest1_tbl.f ld_rate,
datetest1_tbl.f ld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl .fld_date) Between
Forms!aspdash_f orm!date1 And Forms!aspdash_f orm!date2) and
datetest1_tbl.f ld_client IN ("
For i = 0 To listclient.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" & listclient.Colu mn(0, i) &
"', "
End If

Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.de lete "qryMyQuery "
Set qdf = db.CreateQueryD ef("qryMyQuery" , strSQL)

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

Exit_cmdRunQuer y_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_cmdRunQuer y_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.comwro te in
news:11******** **************@ i3g2000cwc.goog legroups.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_Cli ck()
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.f ld_year,datetes t1_tbl.fld_day, datetest1

_tbl.fld_

month,datetest1 _tbl.fld_break_ mins,datetest1

_tbl.fld_break_ hrs,

datetest1_tbl.f ld_date,datetes t1_tbl.fld_clie nt,datetest1

_tbl.f

ld_project,date test1_tbl.fld_s ubproject,datet est1

_tbl.fld_curre

ncy,
datetest1_tbl.f ld_duration_hrs ,datetest1

_tbl.fld_durati on_mins,

datetest1_tbl.f ld_note, datetest1_tbl.f ld_rate,
datetest1_tbl.f ld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl .fld_date) Between
Forms!aspdash_f orm!date1 And Forms!aspdash_f orm!date2) and
datetest1_tbl.f ld_client IN ("
For i = 0 To listclient.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" & listclient.Colu mn(0, i) &
"', "
End If

Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.de lete "qryMyQuery "
Set qdf = db.CreateQueryD ef("qryMyQuery" , strSQL)

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

Exit_cmdRunQuer y_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_cmdRunQuer y_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.comwro te in
news:11******** **************@ i3g2000cwc.goog legroups.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_Cli ck()
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.f ld_year,datetes t1_tbl.fld_day, datetest1

_tbl.fld_

month,datetest1 _tbl.fld_break_ mins,datetest1

_tbl.fld_break_ hrs,

datetest1_tbl.f ld_date,datetes t1_tbl.fld_clie nt,datetest1

_tbl.f

ld_project,date test1_tbl.fld_s ubproject,datet est1

_tbl.fld_curre

ncy,
datetest1_tbl.f ld_duration_hrs ,datetest1

_tbl.fld_durati on_mins,

datetest1_tbl.f ld_note, datetest1_tbl.f ld_rate,
datetest1_tbl.f ld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl .fld_date) Between
Forms!aspdash_f orm!date1 And Forms!aspdash_f orm!date2) and
datetest1_tbl.f ld_client IN ("
For i = 0 To listclient.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" & listclient.Colu mn(0, i) &
"', "
End If
>
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL
>
>
'*** delete the previous query
db.QueryDefs.de lete "qryMyQuery "
Set qdf = db.CreateQueryD ef("qryMyQuery" , strSQL)
>
'*** open the query
'*** DoCmd.OpenQuery "qryMyQuery ", acNormal, acEdit
>
Exit_cmdRunQuer y_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_cmdRunQuer y_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.comwro te in
news:11******** **************@ h48g2000cwc.goo glegroups.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.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" _
& listclient.Colu mn(0, i) _
& "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

'New code to put right after.:
strWhere = strWhere & " AND datetest1_tbl.f ld_employee IN ("
For i = 0 To listemployee.Li stCount - 1
If listemployee.Se lected(i) Then
strWhere = strWhere & "'" _
& listemployee.Co lumn(0, i) _
& "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

>
Thanks!
Bob Quintal wrote:

>"gambit32" <so************ **@gmail.comwro te in
news:11******* *************** @i3g2000cwc.goo glegroups.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_Cli ck()
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.f ld_year,datetes t1_tbl.fld_day, datetest1

_tbl.fld_

month,datetest1 _tbl.fld_break_ mins,datetest1

_tbl.fld_break _hrs,

datetest1_tbl.f ld_date,datetes t1_tbl.fld_clie nt,datetest1

_tbl.f

ld_project,date test1_tbl.fld_s ubproject,datet est1

_tbl.fld_cur re

ncy,
datetest1_tbl.f ld_duration_hrs ,datetest1

_tbl.fld_durat ion_mins,

datetest1_tbl.f ld_note, datetest1_tbl.f ld_rate,
datetest1_tbl.f ld_amount FROM datetest1_tbl "
strWhere = "Where ((datetest1_tbl .fld_date) Between
Forms!aspdash_f orm!date1 And Forms!aspdash_f orm!date2) and
datetest1_tbl.f ld_client IN ("
For i = 0 To listclient.List Count - 1
If listclient.Sele cted(i) Then
strWhere = strWhere & "'" & listclient.Colu mn(0, i)
& "', "
End If

Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
MsgBox strSQL
'*** delete the previous query
db.QueryDefs.de lete "qryMyQuery "
Set qdf = db.CreateQueryD ef("qryMyQuery" , strSQL)

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

Exit_cmdRunQuer y_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_cmdRunQuer y_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