Building Query statement issue (dates)

HCL Domino Domino Forum
1

I’m building an query statement to search a FTIndexed DB. I recently added some date parameters which I cannot seem to get to work (doesn’t find documents…search retuns 0 documents found).

Here’s the funny thing… I debug the code, grab the query line that’s build…pluck into the search bar of a view (only remove the quotes around the statement)and it works (finds correct documents)!

I’m thinking it’s a notation issue…any help would be greatly appreciated…code below:

Dim item1 As NotesItem

Dim item2 As NotesItem

Set item1=tempdoc.GetFirstItem("FromDate")

Set item2=tempdoc.GetFirstItem("ToDate")

	

If tempdoc.FromDate(0) <> ""  And tempdoc.ToDate(0) <> "" Then

	If Query = "" Then

		Query = "FIELD ExpiryDate >= " & item1.Values(0) & "  AND FIELD ExpiryDate <=  " & item2.Values(0)

	Else

		Query = Query + " AND FIELD ExpiryDate >= " & item1.Values(0) &  " AND FIELD ExpiryDate <= " & item2.Values(0)

	End If

End If

Note: Focus on the else line as the Query is already half build when it hits this part of script (with Status = “open”).

Here is the SQL line that’s produced:

“FIELD Status CONTAINS”“Open”" AND FIELD ExpiryDate >= 2000-03-16 AND FIELD ExpiryDate <= 2000-04-16"

Expiry date is a date/time field.

Thanks for any help again

Chris

2

Subject: Building Query statement issue (dates)

Try building your query with square brackets around your dates and see if that helps.

3

Subject: RE: Building Query statement issue (dates)

Thanks Wing. Got it working…due to my stupidity (won’t get into it here…issue with test documents, not code).

For future reference for others…searching for documents between two dates…this code works.

CS

4

Subject: RE: Building Query statement issue (dates)

I’m thinking you need to use / instead of - in those dates, and also may need to use format mm/dd/yyyy – even though obviously that’s not what your computer uses by default. I don’t know why the Notes client search would be different – suspect it’s doing some processing of your input.