Solved Dynamic results based on wildcard dates provided with form fields (1 Viewer)

mrk777

Member
Local time
Today, 19:53
Joined
Sep 1, 2020
Messages
43
Hi Team, I have a form where I have ComboBox with the list of Years (2018,2019,2020,2021) and I have the proper dates in my Database as shown below:

Dates
04/01/2021
03/22/2021
07/22/2020
08/16/2019

Whenever I select any year(example: 2020) in the form, the results should populate the Records between 01/01/2020 till 31/12/2020.

I have tried the following code in the query criteria:
"between #01/01/" & [forms]![frm_Search_Projects]![Cmb_Year] & "And #01/01/" & [forms]![frm_Search_Projects]![Cmb_Year]));

I'm getting an error stating that the expression typed incorrectly or it is too complex to be evaluated.

Please help!! Thanks in advance!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:23
Joined
May 7, 2009
Messages
16,810
use dateserial() function:

Between DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]), 1, 1) And
DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]), 12, 31)
 

mrk777

Member
Local time
Today, 19:53
Joined
Sep 1, 2020
Messages
43
use dateserial() function:

Between DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]), 1, 1) And
DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]), 12, 31)
@arnelgp, I'm getting the same error. I have pasted the SQL query below for your reference.

Note: I have 2 other combo boxes already being used with OR criteria.

Code:
SELECT Master_DataBase.Function_ID, Master_DataBase.Function, Master_DataBase.Project_Lead, Master_DataBase.Project_Name, Master_DataBase.Project_Type, Master_DataBase.Certification, Master_DataBase.Manager_Name, Master_DataBase.Sponsor, Master_DataBase.Champion, Master_DataBase.Project_Start_Date, Master_DataBase.Proposed_Project_End_Date, Master_DataBase.Training_Completed, Master_DataBase.Cost_Included, Master_DataBase.Project_Approval, Master_DataBase.Cost_Approval, Master_DataBase.Dependencies, Master_DataBase.Foresight, Master_DataBase.Status_of_Project, Master_DataBase.Business_Impact, Master_DataBase.Customer_Impact, Master_DataBase.Process_Impact, Master_DataBase.SCORE, Master_DataBase.Activity_Frequency, Master_DataBase.Activity_Frequency_in_No_PerAnnum, Master_DataBase.Current_Activity_Time_InMins, Master_DataBase.Current_Activity_Time_InHrs_PerAnnum, Master_DataBase.Total_Development_Time_InHrs, Master_DataBase.ActivityTime_AfterDevelopment_InHrs_PerAnnum, Master_DataBase.Time_Savings_PerAnnum_InHours, Master_DataBase.Cost_Savings_PerAnnum, Master_DataBase.Test_Result, Master_DataBase.Final_Comments, Master_DataBase.Project_Charter_Status, Master_DataBase.Final_Project_Charter_Saved_in_MSTeams, Master_DataBase.Project_Historical_Received_Status
FROM Master_DataBase
WHERE (((Master_DataBase.Function)=[Forms]![frm_Search_Projects]![Function])) OR (((Master_DataBase.Project_Lead)=[Forms]![frm_Search_Projects]![Project_Lead])) OR (((Master_DataBase.Proposed_Project_End_Date) Between DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]),1,1) And DateSerial(Year([forms]![frm_Search_Projects]![Cmb_Year]),12,31)));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:23
Joined
May 7, 2009
Messages
16,810
what is [Proposed_Project_End_Date] datatype? is it Date/Time?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:23
Joined
May 7, 2009
Messages
16,810
i assume cmb_year is showing "Year" only:

Code:
SELECT Master_DataBase.Function_ID, Master_DataBase.Function, Master_DataBase.Project_Lead, Master_DataBase.Project_Name, Master_DataBase.Project_Type, Master_DataBase.Certification, Master_DataBase.Manager_Name, Master_DataBase.Sponsor, Master_DataBase.Champion, Master_DataBase.Project_Start_Date, Master_DataBase.Proposed_Project_End_Date, Master_DataBase.Training_Completed, Master_DataBase.Cost_Included, Master_DataBase.Project_Approval, Master_DataBase.Cost_Approval, Master_DataBase.Dependencies, Master_DataBase.Foresight, Master_DataBase.Status_of_Project, Master_DataBase.Business_Impact, Master_DataBase.Customer_Impact, Master_DataBase.Process_Impact, Master_DataBase.SCORE, Master_DataBase.Activity_Frequency, Master_DataBase.Activity_Frequency_in_No_PerAnnum, Master_DataBase.Current_Activity_Time_InMins, Master_DataBase.Current_Activity_Time_InHrs_PerAnnum, Master_DataBase.Total_Development_Time_InHrs, Master_DataBase.ActivityTime_AfterDevelopment_InHrs_PerAnnum, Master_DataBase.Time_Savings_PerAnnum_InHours, Master_DataBase.Cost_Savings_PerAnnum, Master_DataBase.Test_Result, Master_DataBase.Final_Comments, Master_DataBase.Project_Charter_Status, Master_DataBase.Final_Project_Charter_Saved_in_MSTeams, Master_DataBase.Project_Historical_Received_Status
FROM Master_DataBase
WHERE (((Master_DataBase.Function)=[Forms]![frm_Search_Projects]![Function])) OR (((Master_DataBase.Project_Lead)=[Forms]![frm_Search_Projects]![Project_Lead])) OR (((Master_DataBase.Proposed_Project_End_Date) Between DateSerial([forms]![frm_Search_Projects]![Cmb_Year],1,1) And DateSerial([forms]![frm_Search_Projects]![Cmb_Year],12,31)));
 

mrk777

Member
Local time
Today, 19:53
Joined
Sep 1, 2020
Messages
43
i assume cmb_year is showing "Year" only:

Code:
SELECT Master_DataBase.Function_ID, Master_DataBase.Function, Master_DataBase.Project_Lead, Master_DataBase.Project_Name, Master_DataBase.Project_Type, Master_DataBase.Certification, Master_DataBase.Manager_Name, Master_DataBase.Sponsor, Master_DataBase.Champion, Master_DataBase.Project_Start_Date, Master_DataBase.Proposed_Project_End_Date, Master_DataBase.Training_Completed, Master_DataBase.Cost_Included, Master_DataBase.Project_Approval, Master_DataBase.Cost_Approval, Master_DataBase.Dependencies, Master_DataBase.Foresight, Master_DataBase.Status_of_Project, Master_DataBase.Business_Impact, Master_DataBase.Customer_Impact, Master_DataBase.Process_Impact, Master_DataBase.SCORE, Master_DataBase.Activity_Frequency, Master_DataBase.Activity_Frequency_in_No_PerAnnum, Master_DataBase.Current_Activity_Time_InMins, Master_DataBase.Current_Activity_Time_InHrs_PerAnnum, Master_DataBase.Total_Development_Time_InHrs, Master_DataBase.ActivityTime_AfterDevelopment_InHrs_PerAnnum, Master_DataBase.Time_Savings_PerAnnum_InHours, Master_DataBase.Cost_Savings_PerAnnum, Master_DataBase.Test_Result, Master_DataBase.Final_Comments, Master_DataBase.Project_Charter_Status, Master_DataBase.Final_Project_Charter_Saved_in_MSTeams, Master_DataBase.Project_Historical_Received_Status
FROM Master_DataBase
WHERE (((Master_DataBase.Function)=[Forms]![frm_Search_Projects]![Function])) OR (((Master_DataBase.Project_Lead)=[Forms]![frm_Search_Projects]![Project_Lead])) OR (((Master_DataBase.Proposed_Project_End_Date) Between DateSerial([forms]![frm_Search_Projects]![Cmb_Year],1,1) And DateSerial([forms]![frm_Search_Projects]![Cmb_Year],12,31)));
Yes @arnelgp
 

Users who are viewing this thread

Top Bottom