在SQL的MS Access字符串错误 [英] String error in SQL MS Access

问题描述

我不知道如果我的头衔是100％准确的，但我认为这是在我的code中的问题。我得到这个code的工作，但是当我更改了我的code等部分，它停止工作。下面是完整的SQL：

 更新CFRRR
  SET assignedto =＆放大器; GetNextAssignee（计划，语言，用户名）及
    assignedby ='
    ＆放大器;窗体！监！NavigationSubform！assignedby.Value
    ＆放大器; '，Dateassigned =＃
    ＆放大器;现在和放大器; ＃，actiondate =＃
    ＆放大器;现在和放大器; ＃，Workername ='
    ＆放大器;使用DLookup（用户名，考勤，用户ID =
    ＆放大器; GetNextAssignee（计划，语言，用户名））
    ＆放大器; '，WorkerID =与＆amp;使用DLookup（用户ID，考勤，用户ID =
    ＆放大器; GetNextAssignee（计划，语言，用户名））
    ＆放大器; WHE​​RE CFRRRID =
    ＆放大器; RS！CFRRRID
 

我正的错误是在这里：

  Workername ='
＆放大器;使用DLookup（用户名，考勤，用户ID =
＆放大器; GetNextAssignee（计划，语言，用户名））
 

这是我得到的输出：

 更新CFRRR SET assignedto = 6，
assignedby ='33'，
Dateassigned = 5＃/ 17 /到2015年下午七时46分40秒＃
actiondate = 5＃/ 17 /到2015年下午7时46分四十秒＃
Workername ='华伦天奴'，
WorkerID = 7 WHERE CFRRRID = 40
 

这是我应该得到的输出：

 更新CFRRR SET assignedto = 6，
assignedby ='33'，
Dateassigned = 5＃/ 17 /到2015年下午七时46分40秒＃
actiondate = 5＃/ 17 /到2015年下午7时46分四十秒＃
Workername ='约翰'，
WorkerID = 6 WHERE CFRRRID = 40
 

这里：

  WorkerID =
＆放大器;使用DLookup（用户ID，考勤，用户ID =
＆放大器; GetNextAssignee（计划，语言，用户名））
 

下面是GetNextAssignee code，以供参考：

 公共功能GetNextAssignee（程序作为字符串，
                              语言作为字符串，
                              用户名作为字符串）只要
        昏暗的分贝作为DAO.DATABASE
        昏暗的RS作为dao.Recordset
        昏暗STRSQL作为字符串
        设置DB = CurrentDb
        STRSQL =SELECT TOP 1
                   用户帐号
                 从上座率为，
                 CFRRR WHERE a.Status ='可用'
                 与a.Programs LIKE CFRRR.program
                 与a.Language = CFRRR.language
                 ORDER BY TS ASC，用户ID，CFRRRID
        设置RS = db.OpenRecordset（STRSQL，dbOpenDynaset）
        如果没有rs.BOF而不是RS.EOF然后
      STRSQL =UPDATE出席
                SET TS =与＆amp; DMAX（[TS]，出席）+1
                ＆放大器; WHE​​RE [用户ID] =放大器; RS！用户ID
                ＆放大器; 和[状态] =可用
            db.Execute STRSQL，dbFailOnError
            GetNextAssignee = RS！用户ID
        其他
            GetNextAssignee = 0
        结束如果

        rs.Close
        db.Close
        设置RS =什么
        设置分贝=什么

    端功能
 

解决方案

更​​改code如下

 暗淡我只要
 

和 AssignNullProjects 的while循环改变这样的查询中

 如果不rs.BOF而不是RS.EOF然后
        虽然没有RS.EOF

I = GetNextAssignee（计划，语言，用户名）

 STRSQL =UPDATE CFRRR SET assignedto =＆放大器; I和，
 assignedby ='＆放大器;！！！形式主管NavigationSubform assignedby.Value
＆放大器; '，Dateassigned =＃与＆amp;现在和放大器; ＃，actiondate =＃
＆放大器;现在和放大器; ＃，Workername ='
＆放大器; _DLookup（用户名，考勤，用户ID =＆放大器; I）
 ＆放大器; '，WorkerID =与＆amp; I和WHE​​RE CFRRRID =＆放大器; RS！CFRRRID
Debug.Print STRSQL
 db.Execute STRSQL，dbFailOnError
  rs.MoveNext
        蜿蜒
    结束如果
 

I'm not sure if my title is 100% accurate, but I think that is the problem in my code. I got this code working, but when I made changes to other portions of my code and it stopped working. Here's the full SQL:

UPDATE CFRRR    
  SET assignedto = " & GetNextAssignee("program", "Language", "username") & ", 
    assignedby = '" 
    & Forms!Supervisor!NavigationSubform!assignedby.Value 
    & "', Dateassigned = #" 
    & Now & "#, actiondate = #" 
    & Now & "#, Workername = '" 
    & DLookup("username", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & "', WorkerID = " & DLookup("userID", "attendance", "userID = " 
    & GetNextAssignee("program", "Language", "username")) 
    & " WHERE CFRRRID = " 
    & rs!CFRRRID

The error I am getting is here:

Workername = '" 
& DLookup("username", "attendance", "userID = " 
& GetNextAssignee("program", "Language", "username"))

This is the output I am getting:

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'Valentino', 
WorkerID = 7 WHERE CFRRRID = 40

This is the output I should be getting:

UPDATE CFRRR SET assignedto = 6, 
assignedby = '33', 
Dateassigned = #5/17/2015 7:46:40 PM#, 
actiondate = #5/17/2015 7:46:40 PM#, 
Workername = 'John', 
WorkerID = 6 WHERE CFRRRID = 40

And here:

WorkerID = "
& DLookup("userID", "attendance", "userID = "
& GetNextAssignee("program", "Language", "username"))

Here is the GetNextAssignee code for reference:

Public Function GetNextAssignee(program As String, 
                              language As String, 
                              username As String) As Long
        Dim db As dao.Database
        Dim rs As dao.Recordset
        Dim strSQL As String
        Set db = CurrentDb
        strSQL = "SELECT TOP 1 
                   userID 
                 FROM attendance as a, 
                 CFRRR WHERE a.Status = 'Available' 
                 AND a.Programs LIKE CFRRR.program  
                 AND a.Language = CFRRR.language 
                 ORDER BY TS ASC, userID, CFRRRID"
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        If Not rs.BOF And Not rs.EOF Then
      strSQL = "UPDATE attendance 
                SET TS= " & DMax("[TS]", "attendance") + 1 
                & " WHERE [userID]=" & rs!userID 
                & " AND [Status]=""Available"""
            db.Execute strSQL, dbFailOnError
            GetNextAssignee = rs!userID
        Else
            GetNextAssignee = 0
        End If

        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing

    End Function

解决方案

Change your code as below

Dim i as long

and inside your while loop of AssignNullProjects change the query like this

If Not rs.BOF And Not rs.EOF Then
        While Not rs.EOF

i = GetNextAssignee("program", "Language", "username")

 strSQL = "UPDATE CFRRR SET assignedto = " & i & ",
 assignedby = '" & Forms!Supervisor!NavigationSubform!assignedby.Value 
& "', Dateassigned = #" & Now & "#, actiondate = #" 
& Now & "#, Workername = '" 
& _DLookup("username", "attendance", "userID = " & i)
 & "', WorkerID = " & i & " WHERE CFRRRID = " & rs!CFRRRID
Debug.Print strSQL
 db.Execute strSQL, dbFailOnError
  rs.MoveNext
        Wend
    End If

这篇关于在SQL的MS Access字符串错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！