Dear contributors,

I apologise if there is a more appropriate group for this query - I can
see relatively few Notes related posts in the VB/VBA groups and
vice-versa.

Currently I have an Excel VBA application that will retrieve all
documents from a Lotus Domino database created after a certain date by
passing a date to the search request in the following way...

Private Sub GetNotesData(crDate1 As String)
Dim LNdate As NotesDateTime
Dim LNdocs As NotesDocumentCollection
Dim LNdoc As NotesDocument

With Application
.StatusBar = "Retrieving recordset from " & SRVR & ":\\" &
DBASE & " - Please be patient..."
.DisplayAlerts = False
End With

' Set the time
Set LNdate = LNsession.CreateDateTime(CDate(crDate1))

' Do the search
Set LNdocs = LNdb.Search(SELEQT, LNdate, 0)

'
'
' Etc.

Is it possible to reduce the recordset (and therefore the horrendously
slow retrieval times) by also passing it a second date, before which
documents were created?

In fact, is there an alternative/better method to use than
CreateDateTime? Currently the session is returning some documents that
were created before crDate1 as though CreateDateTime is possibly the
date and time the document was moved to the particular server/disk it
is being read from.

Br, NickH

wrote in message
oups.com...
Dear contributors,

I apologise if there is a more appropriate group for this query - I can
see relatively few Notes related posts in the VB/VBA groups and
vice-versa.

Currently I have an Excel VBA application that will retrieve all
documents from a Lotus Domino database created after a certain date by
passing a date to the search request in the following way...

Private Sub GetNotesData(crDate1 As String)
Dim LNdate As NotesDateTime
Dim LNdocs As NotesDocumentCollection
Dim LNdoc As NotesDocument

With Application
.StatusBar = "Retrieving recordset from " & SRVR & ":\\" &
DBASE & " - Please be patient..."
.DisplayAlerts = False
End With

' Set the time
Set LNdate = LNsession.CreateDateTime(CDate(crDate1))

' Do the search
Set LNdocs = LNdb.Search(SELEQT, LNdate, 0)

'
'
' Etc.

Is it possible to reduce the recordset (and therefore the horrendously
slow retrieval times) by also passing it a second date, before which
documents were created?

In fact, is there an alternative/better method to use than
CreateDateTime? Currently the session is returning some documents that
were created before crDate1 as though CreateDateTime is possibly the
date and time the document was moved to the particular server/disk it
is being read from.

First of all, you are using db.Search(), which is not only slow but also
very CPU intensive on the server. Full-text index the database and use
db.FTSearch() instead.
Second, why not use a smarter search criteria. If you have a creation date
in the document, you can search using that. I often add a creation date,
since it might happen that a document has to be copied from one database to
another, or something. When you cope a document, the time and date will not
be when the original was created, but when it was copied. So to prevent
problems, I think it is a good practice to maintain a separate creation
date. And you never know when you need to search for a date, like you just
found out. :-)

So assuming you build a full-text index, it should look something like this:

query = "[CreationDate]" & startdate & " AND [CreationDate]<" & enddate
set col = db.FTSearch(query,0)
' Do your stuff here

Notice that you only get 5000 documents return by a query, this can be
changed in the server document, though. So for a big database, it may not
work.

What you could do is to have a categorized view, and use
view.GetAllDocumentsByKey() to get the documents for each date, starting at
the first and ending at the last.

set view = db.GetView("(LookupDocumentsByDate)")
For i = startdate to enddate
set col = view.GetAllDocumentsByKey(Format$(i,"mm/dd/yyyy"))
' Do your stuff here
Next


Good luck!

/Karl

Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
....

@Date(S1Date)28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?

Kind regards, NickH

wrote in message
ps.com...
Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
...

@Date(S1Date)28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?

You are using the wrong syntax for a Full text search. It is documented in
the help.

You should use something like this:

[S1Date]28/10/2006 AND [S1Date]<28/11/2006

or

FIELD S1Date28/10/2006 AND FIELD S1Date<28/11/2006

I prefer the first way.

/Karl

Thanks for trying Karl but if I use square brackets Notes complains
because S1Date is a text field.

I've tried both VBA help and Notes help - neither offer any info on
FTSearch.

I'll have to stick with my current work-around which looks at all the
documents returned by my original query and simply ignores those that
were created outside the specified date range. Its slow but it works.

Kind regards, NickH

wrote in message
ups.com...
Thanks for trying Karl but if I use square brackets Notes complains
because S1Date is a text field.

Bad design. :-)

I've tried both VBA help and Notes help - neither offer any info on
FTSearch.

Huh? In designer: "Help", "Help Topics", expand "LotusScript/COM/OLE
Classes", expand "Lotusscript Classes A-Z", expand "NotesDatabase class" and
click on "FTSearch method":

FTSearch method

Conducts a full-text search of all the documents in a database.

Defined in
NotesDatabase

Syntax
Set notesDocumentCollection = notesDatabase.FTSearch( query$, maxDocs%
[,sortoptions [, otheroptions]] )

Parameters
query$ String. The full-text query. See below for the syntax.
maxDocs% Integer. The maximum number of documents you want returned from
the query. Set this parameter to 0 to receive all matching documents.
sortoptions Integer. Optional. Use one of three constants to specify a
sorting option:
FT_SCORES (8) sorts by relevance score (default).
FT_DATE_DES (32) sorts by document creation date in descending order.
FT_DATE_ASC (64) sorts by document creation date in ascending order.
otheroptions Integer. Optional. Use one of two constants to specify
additional search options:
FT_STEMS (512) uses stem words as the basis of the search.
FT_DATABASE (8192) search includes Domino databases.
FT_FUZZY (16384) searches for related words. Need not be an exact
match.
FT_FILESYSTEM (4096) search includes files that are not Domino
databases.
Note These values are also used with the FTDomainSearch and
UnProcessedFTSearch methods in the NotesDatabase class.

Return value
notesDocumentCollection A collection of documents that match the
full-text query, sorted by the selected option.When the collection is sorted
by relevance the highest relevance appears first. To access the relevance
score of each document in the collection, use the FTSearchScore property in
NotesDocument.

Usage
If the database is not full-text indexed, this method works, but less
efficiently. To test for an index, use the IsFTIndexed property in
NotesDatabase. To create an index on a local database, use the UpdateFTIndex
method.
This method searches all of the documents in a database. To search only
documents found in a particular view, use the FTSearch method in NotesView.
To search only documents found in a particular document collection, use the
FTSearch method in NotesDocumentCollection.
If you don't specify any sort options, you get the documents sorted by
relevance score. If you ask for a sort by date, you don't get relevance
scores. If you pass the resulting DocumentCollection to a NotesNewsletter
instance, it formats its doclink report with either the document creation
date or the relevance score, depending on the sort options you use.
If the database has a multi-database index, you get a multi-database search.
Navigation through the resulting document collection may be slow, but you
can create a newsletter from the collection.

Query syntax
To search for a word or phrase, enter the word or phrase as is, except that
search keywords must be enclosed in quotes. Remember to escape quotes if you
are inside a literal.
Wildcards, operators, and other syntax are permitted. For the complete
syntax rules, see "To use operators to refine a search" in Notes 5 help.

See Also
FTSearch method (in NotesDocumentCollection)
FTSearch method (in NotesView)
FTSearchScore property (in NotesDocument)
IsFTIndexed property
LastFTIndexed property
NotesDocumentCollection class
UnprocessedFTSearch method
UpdateFTIndex method


I'll have to stick with my current work-around which looks at all the
documents returned by my original query and simply ignores those that
were created outside the specified date range. Its slow but it works.

You could also create a view of all documents you want to act on,
categorized by date. Then you use view.GetAllDocumentsByKey() to retrieve
all documents from each day, one date at a time.

Kind regards, NickH

Hi Karl,

Thanks for the response.

I tried the FTSearch (I dont have designer access but the database
happens to be Full Text Indexed) but I get an error saying 'the query
is not understandable'. Here's the query I'm passing it ...

query = "@Date(S1Date)" & CDate(crDate1) & _
" AND @Date(S1Date)<" & CDate(crDate2)

where S1Date is a text field containing the creation date of each
document and crDate1 & crDate2 are String variables containing dates
set prior to runtime by the user. As an example 'query' will resolve to
....

@Date(S1Date)28/10/2006 AND @Date(S1Date)<28/11/2006

Notes returns the error after considering the following line for a
minute...

Set LNdocs = LNdb.FTSearch(query, 0)

Any ideas what I'm doing wrong?

Kind regards, NickH