Search for a date between given ranges - Lotus

Question

I have been trying to work out what is the best way to search for gather all of the documents in a database that have a certain date.

Originally I was trying to use FTsearch or search to move through a document collection, but I changed over to processing a view and associated documents.

My first question is what is the easiest way to spin through a set of documents and find if a date stored in the documents is greater than or less than a specified date?

So, to continue working I implemented the following code.

If (doc.creationDate(0) > cdat(parm1)) 
  And (doc.creationDate(0) < CDat(parm2)) then
  ...
end if

but the results are off

Included! Date:3/12/10 11:07:08 P1:3/1/10 P2: 3/5/10
Included! Date:3/13/10 9:15:09 P1:3/1/10 P2: 3/5/10
Included! Date:3/17/10 16:22:07P1:3/1/10 P2: 3/5/10

You can see that the date stored in the doc is not between P1 and P2. BUT! it does limit the documents with a date less than P1 correctly. So I won't get a result for a document with a date less than 3/1/10

If there isn't a better way than the if statement, can someone help me understand why the two examples from above are included?

Answer 1

Hi you can try something like this:

searchStr = {(Form = "yourForm" & ((@Created > [} & parm1 & {]) & (@Created < [} & parm2 & {])))}

Set docCollection = currentDB.Search(searchStr, Nothing, 0)

If(docCollection.Count > 0)Then
    'do your stuff with the collection returned
End If

Answer 2

Carlos' response is pretty good.

If you have a lot of documents, you can also use a full-text search which will is much faster. The method call is very similar (db.ftsearch(), online help can be found here).

The standard DB Search method operates in the same way as view index updates, so it can get a little slow if you have thousands of documents to search through.

Just make sure you enable full text index for your database in the database properties, (last tab).

Syntax on this approach is very similar, this link provides a good reference for FTsearch. Using Carlos' syntax, you can substitute FTSearch and searchStr assignment for faster searching.