Solved Dynamic results based on wildcard dates provided with form fields

[mrk777]

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]

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]

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.

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]

what is [Proposed_Project_End_Date] datatype? is it Date/Time?

 

[mrk777]

what is [Proposed_Project_End_Date] datatype? is it Date/Time?

Yes!! with mmmm-yyyy format

 

[mrk777]

Yes!! with mmmm-yyyy format

Any help/suggestions on this, please...

 

[arnelgp]

i assume cmb_year is showing "Year" only:

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]

i assume cmb_year is showing "Year" only:

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