Convert string to date : date, convert, string

Hi prettykittyq,

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

Hope this helps

Jaffer

10.24.2004 at 06:15AM PDT, ID: 12392898

Rank: Genius

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

10.24.2004 at 06:19AM PDT, ID: 12392923

Colosseo:

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

10.24.2004 at 06:26AM PDT, ID: 12392955

Rank: Genius

Scott is right. Still:

StartDate2.Value = datStartDate2

/gustav

10.24.2004 at 06:32AM PDT, ID: 12392992

Rank: Genius

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

10.24.2004 at 08:58AM PDT, ID: 12393874

Rank: Genius

shanesuebsahakarn:

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).

10.24.2004 at 09:02AM PDT, ID: 12393888

Rank: Genius

shanesuebsahakarn:

Oh yes - should mention that odding Option Explicit to the top of the module will help troubleshoot these little typos...

10.25.2004 at 03:12AM PDT, 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

10.25.2004 at 03:17AM PDT, ID: 12398695

Rank: Genius

shanesuebsahakarn:

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.

Assisted Solution

10.25.2004 at 03:21AM PDT, ID: 12398707

Rank: Genius

Shane, I don't think you need the CDate anywhy, as long as the fields in CleanCalendar are set to Date/Time

10.25.2004 at 03:46AM PDT, ID: 12398819

Rank: Genius

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

Accepted Solution

10.25.2004 at 04:40AM PDT, 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

10.25.2004 at 04:47AM PDT, ID: 12399139

Rank: Genius

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

Assisted Solution

10.25.2004 at 04:56AM PDT, 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

10.25.2004 at 05:04AM PDT, ID: 12399261

Rank: Genius

> 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

10.25.2004 at 05:14AM PDT, ID: 12399312

Rank: Genius

keep the fields set to date/time, otherwise you will face another issue when using these fields

10.25.2004 at 05:15AM PDT, ID: 12399316

Thanks, gustav and jaffer and shane. It worked just fine once I followed directions.

Kitty

10.25.2004 at 05:20AM PDT, ID: 12399369

Rank: Genius