VBA (access) set data type when creating a table

Question

I have a small problem. I have a VBA macro that creates a table. It works. But Call set data type "short text" and a need a number for column FB_average, Cil_FB and Plneni_FB. Does anyone please have a solution tip?

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

Thanks a lot.

What do you mean by 'another type of short text data (number)'? Short text is not number type. Request doesn't make sense. — June7
Sorry, Call set data type "short text", but a need set data type "number". — Vašek Brožek
You could try CDbl(Nz(SUM([Feedback_summary])/SUM([Feedback_count])) AS FB_average, to force a detection of Double type. — Vincent G
Maybe if you provided a 0 value for the If Null argument of Nz() it would recognize a number and not need conversion function. Why is same calculation repeated 3 times? — June7

Gustav Gustav · Accepted Answer · 2019-11-22T11:29:27

Set a numeric value (0) to replace 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"

VBA (access) set data type when creating a table

1 Answers