Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax for Nested SQL Statement (Access 2000)

    This is my code. I'm erring on the second select statement. I want to do a count of the employeenbr with in the second select statement. What's wrong? Thanks. Sarah
    ==================================================
    Dim sql As String
    Dim keyid As String
    keyid = [Forms]![subfrmApprovedInstallations2]![keyid]
    sql = "SELECT Count(tblApproved.EmployeeNbr) as Count" & _
    "FROM in ("SELECT tblApproved.ProductKeyID, tblApproved.EmployeeNbr, EMPLOYEE.FirstName, EMPLOYEE.LastName" & _
    "FROM tblApproved INNER JOIN EMPLOYEE ON tblApproved.EmployeeNbr = EMPLOYEE.EmployeeNbr" & _
    "WHERE tblApproved.ProductKeyID =" & keyid & ")"

    debug.print sql!Count

    ================================================== ============

    I'm guessing the last debug line is incorrect also. I must have to open a recordset with a source of the sql statement and then retrieve the Count value out of that. Ya think?

  2. #2
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax for Nested SQL Statement (Access 2000)

    Thanks for those who viewed my problem.

    My problem has been solved by using a different method. Rather than in code, I simply created a text box on the form with a control source using the DCount function. This allowed me to specify the field and query I wanted to count the record in.

    Sarah

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Syntax for Nested SQL Statement (Access 2000)

    As a suggestion the next time you are confronted with a SQL problem like that, we usually build those sort of queries using the query designer, then switch to the SQL view, and copy the SQL statement and paste it into VBA. That way we know it compiles correctly. In your case, I think a GroupBy query probably would have worked better, but your new solution certainly works. Isn't Access fun, with all the options available to you!
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax for Nested SQL Statement (Access 2000)

    If it is a continuous form keep an eye on the performance with a large number of records. The domain (such as DCount) functions tend to be quite inefficient compared to queries).

Posting Permissions

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