Check for empty Date in Notes Document

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)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
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?
NTNBowerAuthor Commented:
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.
NTNBowerAuthor Commented:
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?
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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 !

Sjef BosmanGroupware ConsultantCommented:
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".

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NTNBowerAuthor Commented:
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.
Sjef BosmanGroupware ConsultantCommented:
Nice! :-))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.