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

M

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

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)
 
M

mrk777

Member
Local time
Today, 19:53
Joined
Sep 1, 2020
Messages
43
arnelgp said:
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)
Click to expand...
@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

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

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

mrk777

Member
Local time
Today, 19:53
Joined
Sep 1, 2020
Messages
43
arnelgp said:
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)));
Click to expand...
Yes @arnelgp
 
You must log in or register to reply here.

Similar threads

I
Unique Name in Form
Replies
14
Views
468
Pat Hartman
P
smtazulislam
Solved Filter in the subform based on a date
Replies
4
Views
816
smtazulislam
smtazulislam
H
Syntax Error on Dsum Function with date range
Replies
14
Views
520
Gasman
Gasman
A
Solved Looking For Loop To Count Total Actual Days From 2 Dates
2
Replies
38
Views
479
Ashfaque
A
C
Auto generation dates in Access
Replies
4
Views
498
Charlie100
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom