Microsoft Indexing Service query by date [RESOLVED]

[lleemon]

Set up the Microsoft Indexing Service on our server and created a asp.net page to query. We got it working except for quering the results by a start date and end date.

Has anyone else found if you can query by 'Access'?

My sql is:
SELECT doctitle, filename, rank, access
FROM scope()
WHERE CONTAINS('myterm')
AND (Access > '01/09/05' AND Access < '02/09/05')
ORDER BY Rank DESC

If I take out the AND line it works great but the dates just don't seem to catch the right files. Anyone have any good insight on this?

Thanks.

[kleinma]

There is a between syntax like this
AND (Access BETWEEN '01/19/05' AND '02/09/05')

also do you know what kind of database it is that you are querying? If it is access I think you need to maybe use # around the date instead of '

I haven't worked with access databases in a long time.

[lleemon]

kleinma,
Thank you for your response. I don't believe Index Index Server is a database of the types your thinking (Access, Oracle, MS SQL Server, MySQL, etc..) Indexing Service is a service in XP and 2000 only. I am thinking Microsoft does create some type of method like a database in the location you provide when setting up a new Catalog. But I believe the query language is just a bit different then say a T-SQL or SQL used in Microsoft Access. However, I hope I am wrong.

[lleemon]

Well, I think we figured out what the proper way to handle dates:

Dim d as DateTime
....
sString = txtSearch.Text.Replace("'", "''")
sString = sString.Replace(";", vbNullString)
 
sURL = "SELECT doctitle, filename, rank, access FROM scope() "
If radBasic.Checked = True Then
  sURL &= "WHERE FREETEXT('" + sString + "') "
Else
 sURL &= "WHERE CONTAINS('" + sString + "') "
End If
 
If sStartDt.Length > 0 AND sEndDt.Length > 0 THen
  sDtFormat = "yyyy-MM-dd"
  d = txtStartDt.Text
  sStartDt = d.ToString(sDtFormat)
  d= txtEndDt.Text
  sEndDt = d.ToString(sDtFormat)
  sURL &= "AND (Write > '" & sStartDt & "' AND Write < '" & sEndDt & "') "
End If

[kleinma]

I still think you should be able to use between syntax