Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    SQL statement & parameter (Acc 97 sr2 on 95b)

    I have a list box that shows a list of names and the total hours work given to each person. I am trying to write a function so that when a name is clicked in the list, a query will appear showing the jobs that the hours come from.

    Private Sub lstEmptots_Click()
    Dim varItm
    Dim empname
    varItm = lstEmptots.ListIndex
    empname = lstEmptots.ItemData(varItm)

    Dim chosen, strSQL

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim qry As QueryDef
    Set qry = db.QueryDefs("qryJobbyEmp")
    strSQL = "SELECT tblJobAllocate.[Job #], Sum(tblJobAllocate.Hours) AS SumOfHours, tblJobAllocate.Employee From tblJobAllocate GROUP BY tblJobAllocate.[Job #], tblJobAllocate.Employee HAVING ((tblJobAllocate.Employee) = empname) WITH OWNERACCESS OPTION;"
    qry.SQL = strSQL
    DoCmd.OpenQuery "qryJobbyEmp", acViewNormal

    End Sub

    but when I run it, it pops up a parameter window asking for empname.

    once again, what don't I know? (be specific, I realize the list could be endless! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>)
    "Heading for the deep end"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL statement & parameter (Acc 97 sr2 on 95b)

    Change the command:

    strSQL = "SELECT tblJobAllocate.[Job #], Sum(tblJobAllocate.Hours) AS SumOfHours, tblJobAllocate.Employee From tblJobAllocate GROUP BY tblJobAllocate.[Job #], tblJobAllocate.Employee HAVING ((tblJobAllocate.Employee) = empname) WITH OWNERACCESS OPTION;"

    to

    strSQL = "SELECT tblJobAllocate.[Job #], Sum(tblJobAllocate.Hours) AS SumOfHours, tblJobAllocate.Employee From tblJobAllocate GROUP BY tblJobAllocate.[Job #], tblJobAllocate.Employee HAVING ((tblJobAllocate.Employee) = '" & empname & "') WITH OWNERACCESS OPTION;"

    HTH
    pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    NB. I would have answered sooner but I have just been down to Centrelink trying to find a job.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: SQL statement & parameter (Acc 97 sr2 on 95b)

    Pat,

    I actually solved it another way by:

    (((tblJobAllocate.Employee) = [Forms]![frmEmpHours]![lstEmptots]))

    just before your post came in. This way, I do not need to define empname at all, but thanks for the info, once again, getting the syntax exactly right is a real pain.
    "Heading for the deep end"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •