query on two multi-select boxes

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • gambit32
    #1

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

    Tags: None
    • Bob Quintal
      #2
      Re: query on two multi-select boxes

      "gambit32" <somethings.ami ss@gmail.comwro te in
      news:1155339759 .822338.307240@ 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

        Comment

        • gambit32
          #3
          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:
          "gambit32" <somethings.ami ss@gmail.comwro te in
          news:1155339759 .822338.307240@ 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

            Comment

            • gambit32
              #4
              Re: query on two multi-select boxes

              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" <somethings.ami ss@gmail.comwro te in
              news:1155339759 .822338.307240@ 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

                Comment

                • Bob Quintal
                  #5
                  Re: query on two multi-select boxes

                  "gambit32" <somethings.ami ss@gmail.comwro te in
                  news:1155575843 .916790.202710@ 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" <somethings.ami ss@gmail.comwro te in
                  >news:115533975 9.822338.307240 @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

                    Comment

                    Previous template Next
                    Working...