Switch function with different date formats in Access SQL

Hi.

First time trying to use switch function to combine multiple queries to one. For some reason “DateValue” function overrides “DatePart” function in switch clause and gives me wrong format for the week and month parts. This query is for creating month, week and year reports in form.

Is there easy way to get this working? Date fields in table are in short text which i combine in query (C for day, B for month and A for year). Here’s the code:

SELECT Switch(
[Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),
[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A]))
) AS DateFormat, Sum(PK112.E) AS VM_sum, Sum(PK112.F) AS SM_sum, Sum(PK112.G) AS P1_KA_sum, Sum(PK112.H) AS P2_KA_sum, Sum(PK112.I) AS P1_KK_sum, Sum(PK112.J) AS P2_KK_sum, Avg(PK112.K) AS TP_AVG_avg, Avg(PK112.L) AS LP_AVG_avg, Avg(PK112.M) AS LV_AVG_avg
FROM PK112
WHERE ([Forms]![frm_Main]![btn_Pvk] = true)
OR ([Forms]![frm_Main]![btn_vk] = true AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])
OR ([Forms]![frm_Main]![btn_kk] = true AND [PK112.B] = [Forms]![frm_Main].[frm_SubForm]![num_Kk] AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])
OR ([Forms]![frm_Main]![btn_vuosi] = true AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])
GROUP BY Switch(
[Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),
[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A]))
)
ORDER BY Switch(
[Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),
[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),
[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A]))
);

Hi. First time trying to use switch function to combine multiple queries to one. For some reason “DateValue” function overrides “DatePart” function in switch clause and gives me wrong format for the week and month parts. This query is for creating month, week and year reports in form.Is there easy way to get this working? Date fields in table are in short text which i combine in query (C for day, B for month and A for year). Here’s the code: SELECT Switch([Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A]))) AS DateFormat, Sum(PK112.E) AS VM_sum, Sum(PK112.F) AS SM_sum, Sum(PK112.G) AS P1_KA_sum, Sum(PK112.H) AS P2_KA_sum, Sum(PK112.I) AS P1_KK_sum, Sum(PK112.J) AS P2_KK_sum, Avg(PK112.K) AS TP_AVG_avg, Avg(PK112.L) AS LP_AVG_avg, Avg(PK112.M) AS LV_AVG_avgFROM PK112WHERE ([Forms]![frm_Main]![btn_Pvk] = true)OR ([Forms]![frm_Main]![btn_vk] = true AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])OR ([Forms]![frm_Main]![btn_kk] = true AND [PK112.B] = [Forms]![frm_Main].[frm_SubForm]![num_Kk] AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])OR ([Forms]![frm_Main]![btn_vuosi] = true AND [PK112.A] = [Forms]![frm_Main].[frm_SubForm]![val_Vuosi])GROUP BY Switch([Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A])))ORDER BY Switch([Forms]![frm_Main]![btn_pvk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_kk] = true, DateValue([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]),[Forms]![frm_Main]![btn_Vk] = true, DatePart(“ww”, ([PK112.C] & “.” & [PK112.B] & “.” & [PK112.A]), 2),[Forms]![frm_Main]![btn_Vuosi] = true, DatePart(“m”, ([PK112.B] & “.” & [PK112.A]))); Read More

Cart

Cart