Member Avatar for PinoyDev

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 by PinoyDev
  • 1 Contributor
  • 1 Reply
  • 278 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by PinoyDev
Member Avatar for PinoyDev

putting date into parameters solves my problem. Code Below.

cmd.Parameters.Clear()
            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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.