Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: insert into from string error

    i get this error Ms Access db can’t find the input table or query ‘str’

    what is wrong with this syntax?
    Thanks

    Code:
    Private Sub Command167_Click()
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim i As Integer
         Dim str As String
        str = "SELECT qryExportToQBFilter.InternalID, qryExportToQBFilter.Type, qryExportToQBFilter.Price  FROM qryExportToQBFilter WHERE qryExportToQBFilter.InternalID in ("
          For i = 0 To ListExport.ItemsSelected.Count - 1
           str = str & ListExport.ItemData(ListExport.ItemsSelected(i))
           If i = ListExport.ItemsSelected.Count - 1 Then
           str = str & ")"
           Else
           str = str & ","
           End If
         Next i
         
             DoCmd.RunSQL "INSERT INTO tblExportTemp (Type, Price) SELECT Type, Price From str"
    End Sub
    Last edited by Missinglinq; 02-02-15 at 10:16. Reason: Adding [Code] tags

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If strong is a variable then it needs to be added / concatenated to your text.
    Code:
    ...price from " & str
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2004
    Posts
    115
    Str string has a few columns.
    ...Price from " & str didn't work. Any other cherector that I need to add? Or finish?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Did ot work isnt a good enough description if the error.

    as a general rule you are better off assigning the value of your sql to a variable. You can then examine the value of that variable (containing the SQL) either through a message bobox or better yet examine the value within the debugger.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2004
    Posts
    115
    I know it's simple. But I'm not family with this. I'm still getting error can’t find the input table or query ‘str’

    Can you please help assign the value of sql to a variable.?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Check the number of columns in your insert compared to the select (str). They should be identical. It looks to me like you are selecting the internalid but tgat column is not being used in the insert. So you are expecting the SQL engine to coerce 3 columns from the select into two columns on the insert
    check the datatypes
    check the syntax for the insert into SQL statement.
    your current code fails on that.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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