Forum Discussion

juhanik's avatar
juhanik
Copper Contributor
Jun 11, 2024

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]))
);

access
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Jun 11, 2024

    Hi,

     

    String concatenation and DateValue are not reliable ways of obtaining a valid date. If you need to save and use the date parts in this way, you should first create a valid date using the DateSerial function and then apply the other date functions to it. Like this:

     

    DateValue([PK112.C] & ...) --> DateSerial([A], [B], [C])

    DatePart("ww", ([PK112.C] & ...) --> DatePart("ww", DateSerial([A], [B], [C]))

     

    Servus
    Karl
    ****************
    Access Forever
    Access News
    Access DevCon
    Access-Entwickler-Konferenz AEK

Resources