访问数据库追加查询无效的过程调用



我正在尝试运行一个直到现在运行良好的追加查询(我每个月都运行查询)。我现在收到"无效的过程调用"错误,即使没有任何变化。基础select查询运行正常。

下面是append查询的代码 -

INSERT INTO 
    Audit_Vsurvey_All_Data_t 
    ( 
        Department, 
        LAST_NAME, 
        FIRST_NAME, 
        [Last Name], 
        [First Name], 
        [CPM ID], 
        [HP Author], 
        Provider, 
        [Chart ID], 
        Audit_Date, 
        Admit_Date, 
        [Question Group], 
        HP_Contains_Allergies, 
        HP_Contains_Assessment, 
        HP_Contains_Chief_Complaint, 
        HP_Vitals, 
        HP_Contains_Family_History, 
        HP_Contains_HX_Present_Illness, 
        HP_Contains_Diagnostic_Tests, 
        HP_Contains_Medications, 
        HP_Contains_Medical_History, 
        HP_Contains_Surgical_History, 
        HP_Contains_Review_Of_Systems, 
        HP_Contains_Social_History, 
        HP_Contains_Treatment_Plan, 
        HP_Documented_In_30_Days, 
        Charted_Within_24
    ) 
SELECT 
    [TT2870-LA Active].Department_Name & " " & [TT2870-LA Active].SECTION_NAME AS Department, 
    [TT2870-LA Active].LAST_NAME, 
    [TT2870-LA Active].FIRST_NAME, 
    [Vsurvey data to NameSplit].[Last Name],
    [Vsurvey data to NameSplit].[First Name],
    [TT2870-LA Active].[CPM ID], 
    [Vsurvey data to NameSplit].Author, 
    [Vsurvey data to NameSplit].Provider, 
    [Vsurvey data to NameSplit].[Chart ID], 
    [Vsurvey data to NameSplit].Audit_Date, 
    [Vsurvey data to NameSplit].Admit_Date, 
    [Vsurvey data to NameSplit].[Question Group], 
    [Vsurvey data to NameSplit].[H&P contains allergies], 
    [Vsurvey data to NameSplit].[H&P contains assessment/impression], 
    [Vsurvey data to NameSplit].[H&P contains chief complaint], 
    [Vsurvey data to NameSplit].[H&P contains current physical examination], 
    [Vsurvey data to NameSplit].[H&P contains family history], 
    [Vsurvey data to NameSplit].[H&P contains history of present illness], 
    [Vsurvey data to NameSplit]. [H&P contains labs/diagnostic test results], 
    [Vsurvey data to NameSplit].[H&P contains medications], 
    [Vsurvey data to NameSplit].[H&P contains past medical history], 
    [Vsurvey data to NameSplit].[H&P contains past surgical history], 
    [Vsurvey data to NameSplit].[H&P contains review of systems], 
    [Vsurvey data to NameSplit].[H&P contains social history], 
    [Vsurvey data to NameSplit].[H&P contains the treatment plan], 
    [Vsurvey data to NameSplit].[H&P is was documented less than 30 days from admission date_], 
    [Vsurvey data to NameSplit].[H&P present within 24 hours of admission] 
FROM 
    [Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON 
    [Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND 
    [Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME 
WHERE
    [Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#

有没有办法告诉是什么导致了上面的代码,因为我无法发布数据。

非常感谢!

使用适当的缩进格式化SQL代码会发现一些奇怪的问题,我在下面的代码中进行了注释:

INSERT INTO 
    Audit_Vsurvey_All_Data_t 
    ( 
        Department, 
        LAST_NAME, 
        FIRST_NAME, 
        [Last Name], 
        [First Name], 
        [CPM ID], 
        [HP Author], 
        Provider, 
        [Chart ID], 
        Audit_Date, 
        Admit_Date, 
        [Question Group], 
        HP_Contains_Allergies, 
        HP_Contains_Assessment, 
        HP_Contains_Chief_Complaint, 
        HP_Vitals, 
        HP_Contains_Family_History, 
        HP_Contains_HX_Present_Illness, 
        HP_Contains_Diagnostic_Tests, 
        HP_Contains_Medications, 
        HP_Contains_Medical_History, 
        HP_Contains_Surgical_History, 
        HP_Contains_Review_Of_Systems, 
        HP_Contains_Social_History, 
        HP_Contains_Treatment_Plan, 
        HP_Documented_In_30_Days, 
        Charted_Within_24
    ) 
SELECT 
    [TT2870-LA Active].Department_Name & " " & [TT2870-LA Active].SECTION_NAME AS Department, 
    [TT2870-LA Active].LAST_NAME, 
    [TT2870-LA Active].FIRST_NAME, 
    [Vsurvey data to NameSplit].[Last Name],
    [Vsurvey data to NameSplit].[First Name],
    [TT2870-LA Active].[CPM ID], 
    [Vsurvey data to NameSplit].Author, 
    [Vsurvey data to NameSplit].Provider, 
    [Vsurvey data to NameSplit].[Chart ID], 
    [Vsurvey data to NameSplit].Audit_Date, 
    [Vsurvey data to NameSplit].Admit_Date, 
    [Vsurvey data to NameSplit].[Question Group], 
    [Vsurvey data to NameSplit].[H&P contains allergies], 
    [Vsurvey data to NameSplit].[H&P contains assessment/impression], 
    [Vsurvey data to NameSplit].[H&P contains chief complaint], 
    [Vsurvey data to NameSplit].[H&P contains current physical examination], 
    [Vsurvey data to NameSplit].[H&P contains family history], 
    [Vsurvey data to NameSplit].[H&P contains history of present illness], 
    [Vsurvey data to NameSplit]. [H&P contains labs/diagnostic test results], 
                                ^------- Extra space here
    [Vsurvey data to NameSplit].[H&P contains medications], 
    [Vsurvey data to NameSplit].[H&P contains past medical history], 
    [Vsurvey data to NameSplit].[H&P contains past surgical history], 
    [Vsurvey data to NameSplit].[H&P contains review of systems], 
    [Vsurvey data to NameSplit].[H&P contains social history], 
    [Vsurvey data to NameSplit].[H&P contains the treatment plan], 
    [Vsurvey data to NameSplit].[H&P is was documented less than 30 days from admission date_], 
                                                                Should this be here? -------^
    [Vsurvey data to NameSplit].[H&P present within 24 hours of admission] 
FROM 
    [Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON 
    [Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND 
    [Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME 
WHERE
    [Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#

如果上述建议不能解决问题,我只能真正建议,对于要追加的每个字段,您暂时将 select 语句中的表达式替换为该字段的默认值,对所有字段重复,直到查询计算没有错误。

例如,

要测试第一个字段是否导致问题,请将附加值临时更改为构成该字段有效数据的默认字符串值,例如:

INSERT INTO 
    Audit_Vsurvey_All_Data_t 
    ( 
        Department, 
        LAST_NAME, 
        FIRST_NAME, 
        -- ... < more fields > ...
        Charted_Within_24
    ) 
SELECT 
    "Default Department" AS Department, -- Temporarily change to a default value
    [TT2870-LA Active].LAST_NAME, 
    [TT2870-LA Active].FIRST_NAME, 
    -- ... < more fields > ...
    [Vsurvey data to NameSplit].[H&P present within 24 hours of admission] 
FROM 
    [Vsurvey data to NameSplit] LEFT JOIN [TT2870-LA Active] ON 
    [Vsurvey data to NameSplit].[Last Name] = [TT2870-LA Active].LAST_NAME AND 
    [Vsurvey data to NameSplit].LastSplit = [TT2870-LA Active].FIRST_NAME 
WHERE
    [Vsurvey data to NameSplit].Audit_Date Between #10/1/2018# And #12/31/2018#

这将使您能够确定哪些字段导致错误,以便您可以进一步调查。

最新更新