Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert date from string to date/time

Posted on 2008-06-10
3
Medium Priority
?
3,993 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

688 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