Date range search

Classic Servoy
1

Having trouble in searching for records in a date
range. I have two fields in a search table called
sch_low_date and sch_high_date. Keep getting an error
message when the method below is run.

The var dateString output is this
‘11-01-2006…11-15-2006|MM-dd-yyyy’

Which looks OK, but doesn’t work.
Any thoughts would be appreciated.

Best. K

if (sch_low_date == null)
{
var loDate = ‘01-01-1000’
}
else
{
var loDate = utils.dateFormat(sch_low_date,‘MM-dd-yyyy’)
}

if (sch_high_date == null)
{
var hiDate = ‘01-01-3000’
}
else
{
var hiDate = utils.dateFormat(sch_high_date,‘MM-dd-yyyy’)
}

var dateString = “'” + loDate + ‘…’ + hiDate + ‘|MM-dd-yyyy’ + “'”
application.output(dateString)

forms.rslt_rct_search_tab.controller.find()
forms.rslt_rct_search_tab.rct_date = ‘dateString’
forms.rslt_rct_search_tab.controller.search()
controller.sort(‘receipts_to_contacts.con_name_last asc’);

2

I am pretty sure this:var dateString = ```
“'” + loDate + ‘…’ + hiDate + ‘|MM-dd-yyyy’ + “'”

loDate + ‘…’ + hiDate + ‘|MM-dd-yyyy’


And I am certain this:```
forms.rslt_rct_search_tab.rct_date = 'dateString' 
```should be this:```
forms.rslt_rct_search_tab.rct_date = dateString

The first is because you don’t need to enclose the string a ‘second’ time.
The second because you now enter a string value instead of a variable.

3

Hi Kurt

If you are only searching for Dates - and do not need the Time element of the DateTime field it may be simpler to add a stored Calculated field to turn your date into an Integer:

return ( Date.getFullYear() * 10000) + (( Date.getMonth() + 1) * 100) + Date.getDate();

This returns 20061111 for today 11 Nov 06.

For reports on Months I also use:

return ( Date.getFullYear() * 100) + ( Date.getMonth() + 1)

This returns 200611 and avoids the need to know how many days in a given month.

Regards

Graham Greensall
Worxinfo Ltd

4

:lol:

Thank you Marcel and Graham.

Marcel, you were right on both comments. Works like a charm now.

Graham, I will play around with your technique. It looks like it will be very handy in reports.

Thanks again. K