Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: SQL Statement from VBA

    The sql statement contained in this piece of code runs fine as an Append Query from Access, but gives the following error when I try to run it from VBA:

    "Undefined function ORDER_DETAIL_UPDATE.ORDER_NUMBER WHERE in Expression."

    The code I'm using is as follows:

    Function OrderUpdateSQL2()

    Dim db As Database, qdf As QueryDef, strSQL As String

    strSQL = "INSERT INTO ORDER_DETAIL_UPDATE (ORDER_NUMBER, ITEMNO, FirstOfITEM_DESC, 0, 2, 3, 4, 5, 6, 7, 8, 9 )"
    strSQL = strSQL & "SELECT [qryORDER_DET_CONVERT].ORDER_NUMBER, [qryORDER_DET_CONVERT].ITEMNO, [qryORDER_DET_CONVERT].FirstOfITEM_DESC, [qryORDER_DET_CONVERT].[0], [qryORDER_DET_CONVERT].[2], [qryORDER_DET_CONVERT].[3], [qryORDER_DET_CONVERT].[4], [qryORDER_DET_CONVERT].[5], [qryORDER_DET_CONVERT].[6], [qryORDER_DET_CONVERT].[7], [qryORDER_DET_CONVERT].[8], [qryORDER_DET_CONVERT].[9]"
    strSQL = strSQL & "FROM qryORDER_DET_CONVERT LEFT JOIN ORDER_DETAIL_UPDATE ON qryORDER_DET_CONVERT.ORDER_NUMBER = ORDER_DETAIL_UPDATE.ORDER_NUMBER"
    strSQL = strSQL & "WHERE (((ORDER_DETAIL_UPDATE.ORDER_NUMBER) Is Null));"

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = strSQL
    qdf.Execute dbFailOnError

    DoCmd.Quit

    End Function

    Any Ideas?
    Thanks,
    Bill

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Can you debug the string to a msgbox? I noticed there's no spaces between your lines (eg. "WHERE" vs " WHERE").

    Also, try removing one set of paranthesis from the where clause.
    Last edited by Teddy; 06-23-04 at 15:18.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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