I'm trying to take the value in a textbox on a form into a table using an update statement. The textbox value is populated by a calendar control, but when this is passed into the date/time field in the table, something strange is happening.
For example, a date in the textbox of 15/01/2008 will appear as that in the table (i'm using uk format i.e. dd/mm/yyyy). However if the date in the textbox is 10/01/2008 (i.e. the day value is under 12) it appears in the table as 01/10/2008, so has defaulted to mm/dd/yyyy.
I've tried using both CDate and Convert functions, but this seems to fill the table with time data, for example 00:01:10!
By the way i've tried the WE variable in the sql statement with and without the #, and also dim'd the WE as both Date and String, with the same issues.
Dim SubID As String
Dim WE As Date
Dim SQL As String
SubID = [Forms]![frm_Expenses]![SubID]
WE = [Forms]![frm_Expenses]![cboWeekEnding]
SQL = "UPDATE dbo_tbl_Weekly_Expenses_Temp SET [Week Ending] = #" & WE & "# WHERE Submission_ID = " & SubID