Results 1 to 5 of 5
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Coding Problem (A2K SR1)

    I get an error message when running the sql statement. Any ideas why?

    Note: The user selects the name of a table (tName) from a combo box. The purpose of the statement is to check for duplicate CWBS elements in a table. I could do this in other ways, but would require too much more code.


    Dim strSql As String
    Dim tName As String
    tName = Me!Combo40.Value

    MsgBox "CHECK FOR DUPLICATE WBS ELEMENTS", vbInformation, "WBS DUPLICATE CHECK"

    strSql = "SELECT First([" & tName & "].CWBS), Count([" & tName & "].CWBS)" _
    & " From [" & tName & "]" _
    & " GROUP BY [" & tName & "].CWBS" _
    & " HAVING(((Count([" & tName & "].CWBS))>1));"


    Thanks for the help.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coding Problem (A2K SR1)

    Hello Gary,

    I don't see anything wrong with the SQL, but I think the First is unnecessary, since you're grouping on CWBS anyway.

    Since you're using only one table, you can make it a bit easier on yourself by omitting the table name before the field name.

    strSQL = "SELECT CWBS, Count(CWBS) " & _
    "FROM [" & tName & "] " & _
    "GROUP BY CWBS " & _
    "HAVING Count(CWBS)>1;"

    or even

    strSQL = "SELECT CWBS, Count(*) " & _
    "FROM [" & tName & "] " & _
    "GROUP BY CWBS " & _
    "HAVING Count(*)>1;"

    Try displaying the SQL string by inserting
    MsgBox strSQL
    or
    Debug.Print strSQL
    into your code.

    What is the error message you get?

    Regards,
    Hans

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Coding Problem (A2K SR1)

    Thanks Hans,

    I am getting a little rusty here remembering that in a single table pull I don't need all of the references.

    The error message I am getting is "A Run Sql Action requires an argument consisting of a sql statement" ... which typically means I forgot something obvious in the statement.

    Thanks for the MSGBOX strsql idea. ... Haven't tried that yet.

    You gave me some ideas on how to fix this. Let's see if I can get it to work. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coding Problem (A2K SR1)

    RunSQL is for action queries, not for select queries.

    You can:
    <UL><LI>Open a recordset based on a SQL string, or
    <LI>Create a querydef based on a SQL string, and open it using DoCmd.OpenQuery (OpenQuery doesn't work with a SQL string).[/list]HTH,
    Hans

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Coding Problem (A2K SR1)

    ... Thanks Hans. I seem to always forget that the Run Sql only works on actions. I will go to plan B after I replace my faulty brain... <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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