SQL does not update expected records

I have a table of dates which I wish to update to show the fiscal year and month. I have another table which is the recordset RS here which contains Fiscal Year, Fiscal Month, Start date and End date.

The query should update the correct value based on the SQL below. It seems to be updating the wrong records. Any idea's? I have tried to ensure all data types are setup the same. I know the SQL statement alone selects the wrong records but I can't work out why.

Option Compare Database
Function updateDates()
Dim db As Database
Dim rs As Recordset
DoCmd.SetWarnings False
DoCmd.Hourglass True
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblUpdates", 2, 2, 2)
Do While Not rs.EOF
DoCmd.RunSQL "UPDATE VPRECLOG SET VPRECLOG.FY_YR = '" & rs![upFiscalYear] & "', VPRECLOG.FY_MONTH = '" & rs![upFiscalMonth] & "' WHERE (((VPRECLOG.DateCorrected) Between #" & Format(rs![upStartDate], "dd/mm/yyyy") & "# And #" & Format(rs![upEndDate], "dd/mm/yyyy") & "#));"
rs.MoveNext
Loop
rs.Close
db.Close
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Function

SELECT VPRECLOG.FY_YR, VPRECLOG.FY_MONTH
FROM VPRECLOG
WHERE (((VPRECLOG.DateCorrected) Between #1/1/2004# And #2/1/2004#));

SELECT VPRECLOG.FY_YR, VPRECLOG.FY_MONTH
FROM VPRECLOG
WHERE (((VPRECLOG.DateCorrected) Between #1/1/2004# And #2/1/2004#));I assume from the above you are trying to select dates 1-Jan-2004 to 2-Jan-2004.

If this is the case then the error is with your date formats. When you SQL statements with dates between ## you need to use the standard American format mm/dd/yyyy and not the UK dd/mm/yyyy.

You can run a query with either date format

I assume from the above you are trying to select dates 1-Jan-2004 to 2-Jan-2004.

If this is the case then the error is with your date formats. When you SQL statements with dates between ## you need to use the standard American format mm/dd/yyyy and not the UK dd/mm/yyyy.

[Iain] - Hi Mark. Firstly thanks for your response. You are right it interprets all dates as US format, not obvious when the date formats for the field are set to UK. Also when passing a value from a user form I know from passed experience Access handles it in the format it is enetered. The solution to make my code work correctly is to convert the UK date to a US date before passing into the SQL statement.

Between #" & Format(rs![upStartDate], "mm/dd/yyyy") & "# And #" & Format(rs![upEndDate], "mm/dd/yyyy") & "#))

Again thanks for your help.

Iain

Function updateDates()
Dim db As Database
Dim rs As Recordset
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OpenQuery "SetDatesNull", acViewNormal
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblUpdates", 2, 2, 2)
Do While Not rs.EOF
DoCmd.RunSQL "UPDATE tblReceiptLog SET tblReceiptLog.FY_YR = '" & rs![upFiscalYear] & "', tblReceiptLog.FY_MONTH = '" & rs![upFiscalMonth] & "' WHERE (((tblReceiptLog.DateCorrected) Between #" & Format(rs![upStartDate], "mm/dd/yyyy") & "# And #" & Format(rs![upEndDate], "mm/dd/yyyy") & "#));"
rs.MoveNext
Loop
rs.Close
db.Close
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Function