Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statment (2000)

    I'm trying to run this SQL statement in VBA:

    strSQL = "SELECT tblDCList.ID, tblDCList.Sel INTO tmakDCStateList FROM tblDCList " & _
    "WHERE tblDCList.State = '" & strState(i) & "'"
    DoCmd.RunSQL strSQL

    I get no error messages, but the table generated has no records. I put "debug.print strSQL" after the statement, then copied the resultant string into a query, and it worked perfectly.

    What is wrong with the statement?

    Kevin

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: SQL Statment (2000)

    I tried your code on a form of my own and it worked perfectly for me as it is.

    I wasn't sure where you were getting strState(i) from, so I just replace it with strState and hardcoded a value as shown below.

    <pre>Dim strState As String
    strState = "3"

    strSQL = "SELECT tblDCList.ID, tblDCList.Sel INTO tmakDCStateList FROM tblDCList " & _
    "WHERE tblDCList.State = '" & strState & "'"
    DoCmd.RunSQL strSQL
    </pre>

    Regards
    John



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

    Re: SQL Statment (2000)

    If you use DoCmd.RunSQL and if you haven't disabled the standard warning you get when executing an action query, Access should tell you that you're about to insert n records where n is a number. Do you get this message?

Posting Permissions

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