I have the following sub-query that generates payroll report by a given date with format (dd/MM/yyyy) but I do not know why MSAccess automatically changed it to (MM/dd/yyyy) thus I am getting incorrect data. My regional date settings in control panel is set to (dd/MM/yyyy) format as well as the data in the database date fields too. Here is my code.
what_date = Format(date_pass, "dd/MM/yyyy") FirstDayofSelMonth = Format(DateSerial(Year(what_date), Month(what_date), 1), "dd/MM/yyyy") SELECT IIf(IsNull(Sum(tbl_Loan_Schedules_Detail.Credit)),0,Sum(tbl_Loan_Schedules_Detail.Credit)) " & _ "FROM tbl_Loan_Schedules_Detail WHERE tbl_Loan_Schedules_Detail.FileNo = tbl_Employee_Master.Employee_Number " & _ "AND (((ftbl_Loan_Schedules_Detail.Loan_Date)>=#" & FirstDayofSelMonth & "# And (tbl_Loan_Schedules_Detail.Loan_Date)<=#" & what_date & "#))) AS Total_Loan_Deduction
Any help is greatly appreciated..
Edited 1 Year Ago by blocker