Solved

Convert date from string to date/time

Posted on 2008-06-10
3
3,954 Views
Last Modified: 2013-11-27
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
Comment
Question by:grunners80
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 21755447
SET [Week Ending] = #" & format(WE, "yyyy-mm-dd") & "# WHERE
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 21755498
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
 

Author Closing Comment

by:grunners80
ID: 31465940
Many thanks!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question