NTNBower
asked on
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)
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)
ASKER
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.
**************************
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.
ASKER
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?
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?
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 !
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 !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Nice! :-))
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?