Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert string to date

Posted on 2004-10-24
18
Medium Priority
?
106,159 Views
Last Modified: 2012-05-05
I'm working with calendar data exported from Outlook.  For some reason, Outlook exports the data as text, not a date/time variable, so I need to convert the data to date/time before I can work with it.

I tried the following:

Private Sub Clean1_Click()
Dim datStartDate2 As Date
Dim strStartDate As String

StartDate.SetFocus
strStartDate = StartDate.Text
datStartDate2 = CDate(strStartDate)
StartDate2.SetFocus
StartDate2.Text = datStartDate

End Sub

but keep coming up with a type mismatch error.

I also tried DateValue instead of CDate but still got a type mismatch error.

I've tried dim-ing datStartDate2 as Date and as Var but still get that error.

Any suggestions?

Kitty
0
Comment
Question by:prettykittyq
[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
  • 6
  • 4
  • 4
  • +2
18 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392852
Hi prettykittyq,

try
datStartDate2 = Format(strStartDate,"mm-dd-yy")

Hope this helps

Jaffer
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12392898
For the date conversion there is no reason to set focus.
Also, you need to assign to the Value property:

'StartDate.SetFocus
strStartDate = StartDate.Text
datStartDate2 = CDate(strStartDate)
'StartDate2.SetFocus
StartDate2.Value = datStartDate

/gustav
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 12392923
Hi

don't know where this is part of your problem or was just a mis type in your post but you are using

StartDate2.Text = datStartDate

but datStartDate doesnt exist so you need

StartDate2.Text = datStartDate2

Cheers

Scott
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12392955
Scott is right. Still:

StartDate2.Value = datStartDate2

/gustav
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12392992
if you are reffering to a field on a Form then it should be either

[StartDate2]
OR
me.StartDate2

But I assume these are NOT fields, but variables used to get the data from Outlook.

jaffer
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12393874
Why not just assign the value directly rather than going through all of the extra variable declarations?

Me!StartDate2=CDate(Me!StartDate)

I have omitted the .Value since that property is implicit (but so is Me!, it's just a matter of style unless you're using .net).
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12393888
Oh yes - should mention that odding Option Explicit to the top of the module will help troubleshoot these little typos...
0
 
LVL 1

Author Comment

by:prettykittyq
ID: 12398680
Thanks for the input.

I see I didn't give enough information since none of those suggestions helped so let me try again.

The data has been exported from Outlook Calendar to an Excel file which is linked to an Access table.  In Excel, the dates are showing up as:

'10/25/2004

The apostrophe at the beginning is causing Access (and Excel) to see the data as a string rather than numbers.

In an earlier version of this project, we solved that problem in Excel by using a macro based on Excel's DATEVALUE function which "recognizes" the number part of the string and creates the date equivalent.

I'm now trying to achieve the same end with VBA code in Access, so my user doesn't have to run the Excel macro in Excel.

I have a table called Calendar that is the raw data from Excel.  I am trying to take the string in the StartDate field of the Excel-based table Calendar, run it through the appropriate routine, and store the resulting date in the StartDate2 field of a new table CleanCalendar so that Access will recognize it as a date/time field and not a text field.

I tried using Access's DATEVALUE function but kept getting a type mismatch error.

Then I tried using Access's CDATE function but I keep getting a type mismatch error.

The "set focus" code comes up because I'm retrieving the value of the StartDate field of the current row through the StartDate textbox on my form.

So, the task is really:
1.  Retrieve the string StartDate from table Calendar.
2.  Convert the string StartDate into a date/time value.
3.  Store the date/time value in StartDate2 in table CleanCalendar.

If there's a better way to do this, I'm happy to start over but I'm stymied.

As it happens, each record in Calendar has three of these little devils:  StartDate (a string with date information) and StartTime and EndTime (strings with time information), so I'm going to need to do all of them.

Again, thanks but none of your comments have worked so far.

Kitty
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 600 total points
ID: 12398695
Hmm, you could run this SQL statement:

INSERT INTO CleanCalendar (StartDate, StartTime, EndTime) SELECT CDate(Mid$(StartDate,2)), CDate(Mid$(StartTime,2)), CDate(Mid$(EndTime,2)) FROM Calendar

I think that will do it.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12398707
Shane, I don't think you need the CDate anywhy, as long as the fields in CleanCalendar are set to Date/Time
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 600 total points
ID: 12398819
First, are you aware that you can link an Excel worksheet or Named Range as a table in Access?
This would save you the import.

Second, the append query could be something like this:

  INSERT INTO tblCalendarA
    (StartDate,
    StartTime,
    EndTime)
  SELECT
    MID(StartDate, 2),
    MID(StartTime, 2),
    MID(EndTime, 2)
  FROM
    Calendar

/gustav
0
 
LVL 1

Author Comment

by:prettykittyq
ID: 12399098
Shane, the SQL statement looks promising but I don't know how to use it.

Would it be part of a query that I'd run to append the new data to the CleanCalendar table?

Or do I need to create a recordset and run a routine with that?

Sorry, I do need a lot of coaching on this.

Thanks for the help.

Kitty
0
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 800 total points
ID: 12399139
Kitty
Please backup your Data before you do this.

Copy the SQL,
Open a new query,
Menu Bar > View > SQL and paste the SQL
Run the Query using the ! button.

jaffer
0
 
LVL 1

Author Comment

by:prettykittyq
ID: 12399203
Thanks, jaffer.  I tried it and got the error message

"Calendar Project set 240 fields to Null due to a type conversion failure."

The relevant fields in the receiving table (CleanCalnder) were set to date/time.  I went back and changed them to text and that didn't help.

You remarked earlier that I might not need to use CDate.  Should I delete the CDates from the SQL statement?

Kitty
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 12399261
> You remarked earlier that I might not need to use CDate.  Should I delete the CDates from the SQL statement?

Yes, Jaffer is right. Try this:

 INSERT INTO CleanCalendar
    (StartDate,
    StartTime,
    EndTime)
  SELECT
    MID(StartDate, 2),
    MID(StartTime, 2),
    MID(EndTime, 2)
  FROM
    Calendar

/gustav
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12399312
keep the fields set to date/time, otherwise you will face another issue when using these fields
0
 
LVL 1

Author Comment

by:prettykittyq
ID: 12399316
Thanks, gustav and jaffer and shane.  It worked just fine once I followed directions.

Kitty
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12399369
I am glad you got it going,
Thanks for the points and the grade,

Good luck with your project

jaffer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

597 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