Assistance with where clause coding

[theConjurian]

I have a module that will manipulate "appointments" in an Outlook calendar. The calendar entry has a reference number that is keyed to the Access database.

Unfortunately, I cannot use a custom field to hold this reference number as there are other restrictions which prevent this.

I have this reference number in the Message field along with specific details of the appointment.

The message field begins:
Reference: \n Booking No: 1063.1 \n Status: Confirmed ...

To create the record set of objCalendarItems I need an strWhere statement that reads as in the following PSEUDO code:
ii = len (ReferenceNo & "." & LineNo)
strWhere = "Left([Message], 29 + ii) = """ & "Reference: " & "\n" & " Booking No: " & ReferenceNo & "." & No & """"

objAllCalendarItems.Sort "[Message]", True
Set objCalendarItems = objAllCalendarItems.Restrict(strWhere)

This form of code does not work.

Can someone suggest another approach or the correct coding??

 

[dhookom]

My knowledge of the outlook automation is zero but I would add a line to debug strWhere:

ii = len (ReferenceNo & "." & LineNo)
strWhere = "Left([Message], 29 + ii) = """ & "Reference: " & "\n" & " Booking No: " & ReferenceNo & "." & No & """"
debug.Print strWhere    [COLOR=#A40000]'take a look at the variable for troubleshooting[/color]
objAllCalendarItems.Sort "[Message]", True
Set objCalendarItems = objAllCalendarItems.Restrict(strWhere)

You can then open the immediate window to see if the value is as expected.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[theConjurian]

strWhere displays as
Left([Message], 29 + ii) = "Reference:\n Booking No: 1031.1"

Throws the following error:
Runtime error '440':
Condition is not valid.

 

[dhookom]

I got nothing since I don’t know what the syntax should be.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

>strWhere displays as
>Left([Message], 29 + ii) = "Reference:\n Booking No: 1031.1"

But my goal for [tt]strWhere[/tt] is to display:[red]
???[/red]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Outlook is not my story, but starting from Outlook VBA object browser, F1 for Restrict method, redirects here.

AdvancedSearch method is suggested if Restrict is too simple.

MS more info about filtering items and filtering with string comparison for Outlook in links. Some may be helpful in building proper filter.


combo