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 )
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
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...