Date in unbound textbox - format issue?

[RCurtin]

Hi,
I have an unbound form for importing employee hours from a spreadsheet. There is an unbound textbox on it so that the user can enter the week ending date.

The user enters the date and clicks on the Import button. An sql statement is executed to add the employee numbers, hours worked etc and the week ending date to a table called M_Hours.

If the user enters a date like the 13/10/06 (i.e.13 oct) it works fine - the date is shown in the table M_Hours as 13/10/06..

Here is the odd thing - if the user enters 2/10/2006 (i.e. 2 oct) the date in the table is 10/02/06 (i.e. 10 Feb)

I have no idea why this is happening. The format of the textbox is dd/mm/yy as is the format of the WeekEnding field in M_Hours. (From what I've read this should override regional settings? By the way the database is split with the BE stored on the server.)

The following is my code:

Private Sub cmdImportHours_Click()
Dim payrollDate as Date
strPayroll = Me.txtWeekend.Value
    payrollDate = DateSerial(Int(Right(strPayroll, 2)), Int(Mid(strPayroll, 4, 2)), Int(Left(strPayroll, 2)))
   
    If IsDate(payrollDate) = True Then
    setWeekEndDate (CDate(payrollDate))

    '.. other code here to do with importing excel sheet

   DoCmd.RunSQL "INSERT INTO M_Hours ( EmployeeNum, NormalHours, OT1Hours, OT2Hours, WeekEnding )" & _
            "SELECT T_SaltendCooling.EmployeeNum, T_SaltendCooling.ST, T_SaltendCooling.OT1, T_SaltendCooling.OT2, #" & CDate(weekEnd) & "# AS weekend FROM T_SaltendCooling "
End Sub

Public Function setWeekEndDate(PayrollWeekEnd As Date)
    weekEnd = Format(PayrollWeekEnd, "dd/mm/yyyy")
    Debug.Print "setWeekEndDate " & weekEnd
End Function

Any ideas why this is happening?

 

[gemma-the-husky]

your sql statement its taking an american date if there is an ambiguity

in your sql statement wrap the date as format(date,"long date") - that will fix it - actually put the text "long date" in the formatting code.

 

[RCurtin]

Thanks a million for that gemma - it worked perfectly! I spent ages yesterday trying to sort that - thank you.