Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Question Unanswered: SQL syntax problem?!?!

    I have the following code as part of the code for the onclick of a button. I am getting "Missing Operator" every time I try to run it. I can not for the life of me see what I did wrong. Can anyone find what I did/didn't do(I'm sure there's several thing )

    Code:
    strNextID = "(CASE WHEN ((Sites.[ID] from sites) = Len(select max(Sites.[ID]) from sites)= 0) THEN 1 ELSE (select max(Sites.[ID]) from sites)+1) END)"
            strNextSiteControl = "'SC-' & (Right((select DR from disaster),2)) & '-' & Right(1000000 + " & (strNextID) & ",6)"
            strRGSN = "Main.[RGSN ID]"
            strSQL = "INSERT INTO Sites ( ID, SiteControl, [RGSN ID] ) VALUES ('" & strNextID & "','" & strNextSiteControl & "', '" & strRGSN & "')"
            strSQLWhere1 = "WHERE (((MAIN.[RGSN ID]) =  Chr$(34) & [forms]![frmMap_Site]![rgsn id])) & Chr$(34);"
            strSQL = strSQL & strSQLWhere1
            strSQL2 = " UPDATE Sites, MAIN SET MAIN.[SITE CONTROL #] = Sites.SiteControl, MAIN.[DATE ASSIGNED TO SITE INSPECTOR] = Date()"
            strSQLWhere2 = " WHERE (((MAIN.[RGSN ID])= Chr$(34) & [forms]![frmMap_Site]![rgsn id] & Chr$(34)) AND ((Sites.[RGSN ID])= Chr$(34) & [forms]![frmMap_Site]![rgsn id] & Chr$(34)));"
            strSQL2 = strSQL2 & strSQLWhere2
            CurrentDb.Execute strSQL
            CurrentDb.Execute strSQL2
    Thanks!!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    (Sites.[ID] from sites) ?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Nov 2004
    Posts
    51
    I originally had

    ID from Sites

    but changed it when I couldn't figure out what was wrong
    I'm still fairly new to this....

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ID FROM Sites means nothing to the sql engine. "SELECT ID FROM Sites" perhaps? Even then the statement looks funny because you're returning more than one value from a subquery.
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Nov 2004
    Posts
    51
    I tried (Select ID from Sites) with no luck. How would you suggest doing it.

    I need to check the last record to see if it's empty. If it is then return a value of 1(should only happen the very first time the code is ran), if it's not then return the max value + 1 . I didn't use IIF and isnull, because I've read that there are sometimes performance issues when using SQL SERVER backend and I originally had this working with just a couple of queries, but it took 15 minutes everytime someone ran it. So I was hoping to speed things up...

Posting Permissions

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