Evaluate Lotus Notes formula in VB6 for extracting data

Question

I am trying to automate extracting data from a Lotus Notes database into MS Access using VB6

While evaluating the Notes formula, an error pops-up indicating "Run-time error 13 Type mismatch" in the row below.

dtsenttime = domSession.Evaluate(stsenttime, domDocument)

We have some multi value fields: some are text fields and some are date fields. I don't know how to get the data from multi value fields.

Private Sub Command1_Click()
Dim domSession As New Domino.NotesSession
Dim domDatabase As New Domino.NotesDatabase
Dim domDocument As NotesDocument
Dim domViewEntry As NotesViewEntry
Dim domcollection As NotesDocumentCollection
Dim domform As NotesForm
Dim domView As NotesView
Dim domViewNav As NotesViewNavigator

Dim dtsenttime As Date
Dim stsenttime As String
Set domDatabase = domSession.GetDatabase("xxxxxxxxxx", "yyyyyyyyyy.nsf")
Set domView = domDatabase.GetView("02.Charges")
Set domViewNav = domView.CreateViewNav
Set domViewEntry = domViewNav.GetFirstDocument()

stsenttime = "@If(JobSentTimeFinalDelivery="""";"""";@Max(JobSentTimeFinalDelivery))"                            
dtsenttime = domSession.Evaluate(stsenttime, domDocument)

Answer 1

In your sample code, you haven't instantiated and initialised an instance of NotesDocument.

I notice that the docs say about the doc parameter of the Evaluate method:

doc

NotesDocument. The formula context. Cannot be null.

Answer 2

Elaborating on per Mark Bertenshaw's response, try this:

dtsenttime = domSession.Evaluate(stsenttime, docViewEntry.Document)

Re multi-value fields, note that dtsenttime will come back as a variant, so you need to change your dim statement:

Dim dtsenttime As Variant

Even when the evaluated formula resolves to just a single value, dtsenttime will resolve to an array with a single element. If you evaluate a formula that returns a list of multiple values, then dtsenttime will resolve to an array containing all the values. (Some versions of IBM's doc on this are confusing, in that they say "A scalar result is returned." but that is truncated from what other versions say: "A scalar result is returned in the first element.")

Answer 3

You can also skip Evaluate alltogether.

First of all, as Mark pointed out, you haven't set domDocument to anything.

I would then use the following code:

Dim domTemp As Variant
Dim domValues(0) As Date 
Dim i as Integer
Dim maxDate As NotesDateTime
'*** Get array of values from field
domTemp = domDocument.GetItemValues("JobSentTimeFinalDelivery")
'*** Redim array to match number of values in field
Redim domValues(Ubound(domTemp)) As Double
'*** Populate new array
For i = Lbound(domTemp) to Ubound(domTemp)
    domValues(i) = domTemp(i)
Next
'*** Use the VB Max() method to get the largest value of the arrays
maxDate = Cdat(domValues.Max)

You may be able to skip using the second array, and use Max() directly on the domTemp array, I don't know how VB handles an array initially declared as a Variant.