[Webinar] Streamline your web hosting managementRegister Today


Check for empty Date in Notes Document

Posted on 2008-01-31
Medium Priority
Last Modified: 2013-12-18
Building a search string in LotusScript.  One of the conditions is that there is no date paid.  The Date_Paid field is defined as a Date/Time field on the document.

This works
strSearch = |((FORM = "Document") & (Doc_Type = "Invoice"))|

This doesn't
strSearch = |((FORM = "Document") & (Doc_Type = "Invoice") & (Date_Paid = ""))|      

How do you check for an empty date field in a Notes search string?

Here is the code in the section.
Dim ndtStartDate As New NotesDateTime(Today - 120)
' Select Invoice documents within the date range
Dim strSearch As String
strSearch = |((FORM = "Document") & (Doc_Type = "Invoice") & (Date_Paid = ""))|      
Dim dcNotes As NotesDocumentCollection
Set dcNotes = dbNotes.Search(strSearch, ndtStartDate,0)

Question by:NTNBower
  • 3
  • 3
LVL 46

Expert Comment

by:Sjef Bosman
ID: 20790960
If it's for a db.Search, then datefield="" will do. In a db.FTSearch, it is not possible to look for empty fields AFAIK.

Now you say it is "defined as a Date/Time field on the document". Technically speaking, that's not possible. I suppose you want to say: it is "defined as a Date/Time field on the form". A document doesn't have to contain values that correspond with the form mentioned in the document. A form is only used to display a document's values. If somehow these valiues do not correspond enough with the fieldtypes on the form, some error will be displayed.

Could you please verify the value of Date_Paid IN a document that should be found by the query? To do that, locate the document in a view, DON'T open the document, and open the Document Properties window (Alt-Enter). Second tab, then locate Date_Paid. What's the value in that field, and of what type is it?

Author Comment

ID: 20791159
It appears to be a text field with a value of "".  Here is the text from the design tab.
Field Name: Date_Paid
Data Type: Text
Data Length: 0 bytes
Seq Num: 3
Dup Item ID: 0
Field Flags: SUMMARY


This is a database search, but the Date_Paid = "" check did not seem to be working, although that could be because I had the syntax wrong when I built the search string.  What I put in the code is different than what I find in the debug variable window.

Author Comment

ID: 20792061
I think I have found the problem and need help with the fix.  In the design of the document, I had the Date_Paid type set to Date/Time, but I had the Style set to Calendar/Time Control.  I changed the Style to Notes and refreshed the design of the database with the new template.  It seemed to sort better that way, and the Data Type in the field properties for Date_Paid is now Time/Date.  For some, but not all of the records.

Firsdt, does this sound like a solution for the sorting problem.

Second, do I need to write an Agent to set every record's Data Type to Time/Date?
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

LVL 63

Expert Comment

ID: 20793805
To your second questopn, yes. you need to convert.

For your first question, you might consider placing a non blank default date in the field, that is hidden if it is the default, so that it will be easier to conduct a search.

I hope this helps !

LVL 46

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 20795037
I think I'd like to disagree with my learned colleague here:
1) No, it doesn't sound like a solution, for the form is irrelevant when searching, since an empty date-field always has the text-value "".
2) Therefore, you can't set empty date-fields to a date-type.

Instead of Date_Paid="", could you try

      strSearch = |((FORM = "Document") & (Doc_Type = "Invoice") & !@IsTime(Date_Paid))|      

If it doesn't work with that query, I think there's something else wrong: documents with a differenc Doc_Type or so, e.g. "INVOICE" instead of "Invoice".

Author Closing Comment

ID: 31427126
Thanks.  That was exactly what I needed.  Good point about "Invoice."  Fortunately the input is from a VB app so I can control the field.
LVL 46

Expert Comment

by:Sjef Bosman
ID: 20797363
Nice! :-))

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question