Question

I'm trying to use a query search to find data that is from before today's date to find old events that my department has held. In addition, I have a search that does the inverse of this and finds all events newer than today to find events in the future.

Each event topic has a form, EventForm, which lives in the same web (Main) as the event topics. EventForm contains the field Date, which is what I compare against.

The documentation of query search indicates that I can use d2n() to convert dates to epoch times and use this for comparison. This does not seem to be the case.

I have a future test event, where the date is set to "31 Dec 2009", and a previous test event, where it is set to "1 Jan 2007".

My search reads like this:

%SEARCH{"parent.name = '%TOPIC%' AND d2n(EventForm.Date) < d2n('%DATE%')" web="%WEB%" type="query"}%

The search returns the header: "Searched: parent.name = 'Events' AND d2n(EventForm.Date) < d2n('28 Jan 2008')"

Which is what I expected it to do. However, it both the future and past events appear in the search, when only the past event should be there. If I remove the "parent.name" element of the search, it will return all topics in the web. I believe it is simply returning any value with a Date in it (and every topic has one of those).

The only way this search will work is if I do this:

%SEARCH{"parent.name = '%TOPIC%' AND d2n(fields[name='Date'].value) >= d2n('%DATE%')" web="%WEB%" type="query"}%

Here, "fields" is the only thing that will work. If I specify EventForm[name='Date'].value, the future search returns zero results, and the past search returns all topics in the web. If I remove the parent.name clause, it will return the correct future search results, but the past search results returns every topic in the web. So it is not correctly limiting itself to looking in EventForm only. I can use this as a temporary workaround for now, but I'd like to find a solution.

I've tried various date formats, various shorthand and longhand specifications of the Date field for query search, and tried changing the date field to be called EventDate instead. Nothing has worked correctly, only specifying "fields" seems to work. I am sure this is something obvious I am missing, but I cannot see it.

Any help would be very much appreciated.

Environment

-- ChrisFLewis - 28 Jan 2008

Answer

If you answer a question - or have a question you asked answered by someone - please remember to edit the page and set the status to answered. The status is in a drop-down list below the edit box.

Sorry, closing this question after more than 30 days of inactivity. Feel free to re-open if needed.

-- PeterThoeny - 02 Mar 2008