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
– June7, Commented Nov 22, 2019 at 9:27
Sorry, Call set data type "short text", but a need set data type "number". — Vašek Brožek
– Vašek Brožek, Commented Nov 22, 2019 at 9:34
You could try CDbl(Nz(SUM([Feedback_summary])/SUM([Feedback_count])) AS FB_average, to force a detection of Double type. — Vincent G
– Vincent G, Commented Nov 22, 2019 at 9:56
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
– June7, Commented Nov 22, 2019 at 10:12

Vincent G · Accepted Answer · 2019-11-22 13:27:32Z

0

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"

edited Nov 22, 2019 at 13:27

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

answered Nov 22, 2019 at 11:29

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA (access) set data type when creating a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related