Good day!

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..

Thank you!

Edited 1 Year Ago by blocker

putting date into parameters solves my problem. Code Below.

            cmd.Parameters.Add("@FirstDayofSelMonth", OleDbType.Date).Value = DateSerial(Year(date_pass), Month(date_pass), 1)
            cmd.Parameters.Add("@date_pass", OleDbType.Date).Value = Format(date_pass, "dd/MM/yyyy")

Thank you!

This question has already been answered. Start a new discussion instead.