vba - 创建表时的VBA（访问）设置数据类型

Question

我有一个小问题。我有一个创建表的 VBA 宏。有用。但是调用集数据类型“短文本”并且需要列 FB_average、Cil_FB 和 Plneni_FB 的数字。有人请有解决方案提示吗？

Call generuj_t_vystup("t_prod_work_time_fb.Skill,t_prod_work_time_fb.Skill_KM,t_prod_work_time_fb.Locality,t_prod_work_time_fb.Team,t_prod_work_time_fb.Spv_name,t_prod_work_time_fb.Agent_name,t_prod_work_time_fb.Agent_login,t_prod_work_time_fb.Rok,t_prod_work_time_fb.Kvartal,t_prod_work_time_fb.Měsíc,t_prod_work_time_fb.Tyden,t_prod_work_time_fb.Event_date", "t_vystup_fb_denni_agent")    

Public Function generuj_t_vystup(hlavicka As String, vystupni_tabulka As String)
    SQL = "Select "
    SQL = SQL & hlavicka & ", "
    SQL = SQL & "Nz(SUM([Feedback_summary])) AS FB_summary,"
    SQL = SQL & " Nz(SUM([Feedback_count])) AS FB_count,"
    SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count])) AS FB_average,"
    SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count])) AS Cil_FB,"
    SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count])) AS Plneni_FB"
    SQL = SQL & " INTO "
    SQL = SQL & vystupni_tabulka
    SQL = SQL & " FROM t_prod_work_time_fb "
    SQL = SQL & " GROUP BY "
    SQL = SQL & hlavicka

    spustsql SQL

    End Function

Sub spustsql(SQL As Variant)
On Error GoTo chyba

DoCmd.RunSQL SQL
Exit Sub

非常感谢。

标签： vbams-access

Answer 1

设置一个数值(0) 来替换Null：

SQL = SQL & " Nz(SUM([Feedback_summary]), 0) AS FB_summary,"
SQL = SQL & " Nz(SUM([Feedback_count]), 0) AS FB_count,"
SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count]), 0) AS FB_average,"
SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count]), 0) AS Cil_FB,"
SQL = SQL & " Nz(SUM([Feedback_summary])/SUM([Feedback_count]), 0) AS Plneni_FB"