ReminderTime problem

[Richard M]

I am having problems with creating a reminder date in Outlook from Access. The time is working ok but the date goes from 11-29-2010 to 12-29-2010 when it gets to Outlook.

I found this answer in a thread from Ian:

.ReminderTime = me!ApptDate & " " & me!ApptTime

When I used it, the time comes through ok but the date does not. Can anybody explain what I did wrong? Here is my code:

.ReminderTime = Forms!frmShowAddOutlookTask!txtReminderDate & " " & Forms!frmShowAddOutlookTask!txtReminderTime

Note I did a debug.print on Forms!frmShowAddOutlookTask!txtReminderDate and it showed me the date I enetered on the form as 11-29-2010. But Outlook shows it as 12-29-2010. The code is in a module.

Thanks for the help,

Richard

 

[darbid]

I must admit my first impression is that you have VBA fairies that are changing your code.

There are 2 things that you can try and while trying them I think you will see something else that is causing the problem.

For me I give Outlook date time properties 2 different kinds of values.

sometimes I set it out as a string eg

.ReminderTime = Format(dt_startdate, "Short Date") & " " & Format("9.00", "Short Time")

Where dt_startdate is a variable of type DATE.

or if I have to manipulate dates (maybe because i want to add a day or something first) then I first make sure I have a date variable with the correct date and time. Then I simply use that for the outlook property.

.ReminderTime = dt_remindertime

I think ReminderTime is used for Tasks. Thus have a look here - you need to give it a "Date"

http://msdn.microsoft.com/en-gb/library/aa171904(office.11).aspx

 

[gemma-the-husky]

do you have two separate fields for date and time

maybe the time is actually storing the value for one day plus the offset time.

you would never know until you tried to use it.

try msgbox(val(thetime)) and see if it comes up as 1.something or 0.something.

 

[Richard M]

Maybe the VBA fairies are real darbin. Here is some more info. I am using Outlook 2007 and creating a Task.

Still not getting the right date.

Input from form:

Reminder Date: 11-20-2010
Reminder Time: 9:00 AM

Outlook Task:

Reminder Date: 12-30-2010
Reminder Time: 9:00 AM (This is working OK)

Here is the full code:

Function fncAddOutlookTask()
    Dim strAnsDate As String
    Dim strAnsTime As String
     Dim OutlookApp As Outlook.Application
     Dim OutlookTask As Outlook.TaskItem
    strAnsDate = Forms!frmShowAddOutlookTask!txtReminderDate
    strAnsTime = Forms!frmShowAddOutlookTask!txtReminderTime
     Set OutlookApp = CreateObject("Outlook.Application")
     Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
    
     With OutlookTask
         .Subject = Forms!frmShowAddOutlookTask!OLSubject
         .Body = Forms!frmShowAddOutlookTask!OLNotes
         .ReminderSet = Forms!frmShowAddOutlookTask!OLReminderONOff
         .ReminderTime = Format(strAnsDate, "short date") & " " & Format(strAnsTime, "short time")
         .StartDate = Forms!frmShowAddOutlookTask!OLStartDate
         .DueDate = Forms!frmShowAddOutlookTask!OLDueDate
         
         .PercentComplete = 10
         .Categories = Forms!frmShowAddOutlookTask!OLCategories
         .Companies = Forms!frmShowAddOutlookTask!OLCompanies
         
         .Status = Forms!frmShowAddOutlookTask!txtPriority
         
         
         .Save
     End With
 End Function

gemma-the-husky what is "thetime" in msgbox(val(thetime)) refers to?

Richard

 

[darbid]

I would make this a date

Dim strAnsDate As Date

Otherwise you have got me beat - you have fairies and pixies and they are having a bloody party by dancing on your tables.

I assume you are in the US and have a US regional settings right?

I cannot see anything wrong maybe someone else does.

I would suggest you put a heap of debug.prints around - like check everything that happens with your dates from here

how are you checking that the user puts a proper date in txtReminderDate?

strAnsDate = Forms!frmShowAddOutlookTask!txtReminderDate
strAnsTime = Forms!frmShowAddOutlookTask!txtReminderTime

Also I assume you are looking at the date time in outlook.

do a debug.print .ReminderTime after you set it and after you .save to check what value it has then.

Try to determine exactly when the change in this date is.

 

[HiTechCoach]

I thin kthe problem is here :

.ReminderTime = Format(dt_startdate, "Short Date") & " " & Format("9.00", "Short Time")

with the format() which convert the vaules to a string NOT a daee/time data type.


Also this is a problem: Format("9.00", "Short Time")
There should be a ":: not a ".".

Try:

.ReminderTime = Format(dt_startdate, "Short Date") & " " & Format("9:00", "Short Time")

or

.ReminderTime = Format(dt_startdate, "Short Date") & " " & Format(TimeSerial(9,0,0), "Short Time")

or keeping the date/time data type try

.ReminderTime = dt_startdate + TimeSerial(9,0,0)

 

[darbid]

Also this is a problem: Format("9.00", "Short Time")
There should be a ":: not a ".".

Thats interesting I did not know that. For me a "." works.

Thanks I will have to check that it does not cause any problems later in my code.

In any case he is not using it.

 

[Richard M]

darbin,

Yes, I am in the USA.

I did some math and the dates from input to Outlook is 40 days. I tried it several times and it is always 40 days.

I will be putting the debug.print all over and see what I can find. Or I could subract 40 days from the reminder date before taking it to Outlook.

I will let you know what happens.

by the way the is 9.00 set up on how your systems shows time?

Richard

 

[gemma-the-husky]

Maybe the VBA fairies are real darbin. Here is some more info. I am using Outlook 2007 and creating a Task.

Still not getting the right date.

Input from form:

Reminder Date: 11-20-2010
Reminder Time: 9:00 AM

Outlook Task:

Reminder Date: 12-30-2010
Reminder Time: 9:00 AM (This is working OK)

Here is the full code:

Function fncAddOutlookTask()
    Dim strAnsDate As String
    Dim strAnsTime As String
     Dim OutlookApp As Outlook.Application
     Dim OutlookTask As Outlook.TaskItem
    strAnsDate = Forms!frmShowAddOutlookTask!txtReminderDate
    strAnsTime = Forms!frmShowAddOutlookTask!txtReminderTime
     Set OutlookApp = CreateObject("Outlook.Application")
     Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
    
     With OutlookTask
         .Subject = Forms!frmShowAddOutlookTask!OLSubject
         .Body = Forms!frmShowAddOutlookTask!OLNotes
         .ReminderSet = Forms!frmShowAddOutlookTask!OLReminderONOff
         .ReminderTime = Format(strAnsDate, "short date") & " " & Format(strAnsTime, "short time")
         .StartDate = Forms!frmShowAddOutlookTask!OLStartDate
         .DueDate = Forms!frmShowAddOutlookTask!OLDueDate
         
         .PercentComplete = 10
         .Categories = Forms!frmShowAddOutlookTask!OLCategories
         .Companies = Forms!frmShowAddOutlookTask!OLCompanies
         
         .Status = Forms!frmShowAddOutlookTask!txtPriority
         
         
         .Save
     End With
 End Function

gemma-the-husky what is "thetime" in msgbox(val(thetime)) refers to?

Richard

what I meant was

put some msgboxes in there at appropriate points to show you what data you have, and how it is changing

and/or

put a breakpoint in and step through the code.

I am convinced one of your date/time variables has a stray value in there.


-------------
times and dates are actually stored as real numbers

the number before the decimal is a day count. the number after a time portion

so 0.5 is half a day or 12hours.

maybe msgbox(cdbl(mytimevariable)) is more accurate, to see the real data being stored.

 

[Richard M]

darbin and gemma-the-husky,

I am thinking my problem is in Outlook. The only way I can test it is to put the program on the computer it will go on and run it. Here is the reason I think it is in Outlook.

The two debug.print shows that the date and time going to .reminderTime is correct and the .remindertime is correct.

form time 1:11/15/2010 10:00
ReminderTime 1: 11/15/2010 10:00:00 AM

When I did a msgbox(cdbl(mytimevariable)), I got the the date and time code. The time code was 40497.4166666667 and I put it into Excel and guess what the date was 11-15-2010. So somewhere in Outlook, it is adding 40 days.

Now to solve this problem I did:

strAnsDate = Forms!frmShowAddOutlookTask!txtReminderDate - 40

When I ran the program it gave the propper date number and then Outlook gave the proper Date I am looking for. The time part of the date number is correct.

When I install the program it may work differently. It will be installed on several computers.

If anybody can tell me why 2007 Outlook is working this way, I would like to know.

Thanks for helping me and have a good weekend.

Richard

 

[gemma-the-husky]

put a breakpoint in the code at the top, and step through with F8. hover over any variable to see what is there.

 

[darbid]

Try changing your order of setting your properties. Set start and due dates first. Then set reminder.

 

[darbid]

Also to avoid other problems, set it to Now() so we can cut down on the possible problems.

 

[Richard M]

Well I stepped through it this morning and then I moved the .remindertime past the start and due dates and guess what?

The remindertime follows through to Outlook.

I had the computer off all weekend and I think the fairies left.

Thank you all for all the help. Now I have to work with appointments. I got part of it worked out and from what I learned, I am well on my way.

Thanks,
Richard

 

[darbid]

Well I stepped through it this morning and then I moved the .remindertime past the start and due dates and guess what?

I told you, you had VBA fairies....in this case the worst kind Outlook ones.