• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4003
  • Last Modified:

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.

Thanks
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

0
grunners80
Asked:
grunners80
1 Solution
 
peter57rCommented:
SET [Week Ending] = #" & format(WE, "yyyy-mm-dd") & "# WHERE
0
 
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

or

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

Mike
0
 
grunners80Author Commented:
Many thanks!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now