Convert date from string to date/time

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
DoCmd.RunSQL (SQL)

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

peter57rConnect With a Mentor Commented:
SET [Week Ending] = #" & format(WE, "yyyy-mm-dd") & "# WHERE
Mike EghtebasDatabase and Application DeveloperCommented:
if [Week Ending] in your table is text, use:

SET [Week Ending] = '" & format(WE, "yyyy-mm-dd") & "' WHERE

if [Week Ending] in your table is date/time, use:

SET [Week Ending] = #" & WE  "# WHERE


SET [Week Ending] = #" & Cdate(WE)  "# WHERE

grunners80Author Commented:
Many thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.