I am trying to do a search on a database to return a document collection so I can send a newsletter out warning users that a letter is due to go out. The letters must be sent 4 weeks after the document was created. I have a field on the form that calculates when that date is. I have an extensive search string to find the relevant documents and the bit that isn't working is the part that compares todays date to the 4 week due date. My code (stripped out the search criteria that was working) is:
Dim tempdate As Variant
tempdate = Evaluate("@Today")
Searchb = " FIELD FourWeekDue = " & Tempdate(0) & ""
Set dcFour = db.FTSearch(Searchb, 0, FT_DATE_DES )
This should return 2 documents and it doesn't. If I force the date in as UK date format dd/mm/yyyy:
Searchb = " FIELD FourWeekDue = 08/10/2004"
It doesn't work, but if I change it to US format mm/dd/yyyy
Searchb = " FIELD FourWeekDue = 10/08/2004"
It works. However all the servers are in the UK, about half are on UK format and half on US (don't ask me why), it's the same problem on all servers. I've tried everyway possible to try and get it to see todays date in the correct format and it won't work. Anyone have any ideas?