I have indexed my tables and created my forms to utilize LQO. When i execute my query from a button it filters the browse on my form approriately.
Any query i use that is referencing an indexed character field seems to use LQO and the response is instant. No problems there.
However when I try to query a Date field and use the between() or between_date() commands as per what I have read in these forums and user guide, it doesn't seem to be utilizing the LQO as I notice it counting through the records in the status bar (for example 200 of 500 records 40%) and doesn't respond as quick as the indexed character fields.
I think it is just a simple syntax problem or I am missing the point in my design
In my table I have indexed a date field called 'completeddate' (there are no filters or unique only settings)
On my form I have two variables (date type) called 'date1' and 'date2'. I have a browse showing all the records in my table. I have button that has the script to do the filter.
Below are the scripts I have tried to filter my records.
1.
dim begin_date as D
dim end_date as D
begin_date = date1
end_date = date2
tbl = table.current()
query.filter="between(completeddate,{"+dtoc(date1)+"},{"+dtoc(date2)+"})"
query.order = "invert(completeddate)"
ix = tbl.query_create()
parentform.refresh_layout()
2.
dim begin_date as D
dim end_date as D
begin_date = date1
end_date = date2
tbl = table.current()
query.filter = between_date("completeddate",var-"begin_date,var-"end_date)
query.order = "invert(completeddate)"
ix = tbl.query_create()
parentform.refresh_layout()
* I have also tried variations of these scripts that I thought might work with no luck, I even tried hard coding the dates instead of using variable
These scripts both filter the browse correctly except I don't think it is utilizing LQO as per response being slower than with character fields.
Is this normal or have I got it all wrong??
Just an observation when I do a query just based on referencing the completeddate date field it seems to utilize the LQO.
i.e. query.filter="completeddate = begin_date"
Any query i use that is referencing an indexed character field seems to use LQO and the response is instant. No problems there.
However when I try to query a Date field and use the between() or between_date() commands as per what I have read in these forums and user guide, it doesn't seem to be utilizing the LQO as I notice it counting through the records in the status bar (for example 200 of 500 records 40%) and doesn't respond as quick as the indexed character fields.
I think it is just a simple syntax problem or I am missing the point in my design
In my table I have indexed a date field called 'completeddate' (there are no filters or unique only settings)
On my form I have two variables (date type) called 'date1' and 'date2'. I have a browse showing all the records in my table. I have button that has the script to do the filter.
Below are the scripts I have tried to filter my records.
1.
dim begin_date as D
dim end_date as D
begin_date = date1
end_date = date2
tbl = table.current()
query.filter="between(completeddate,{"+dtoc(date1)+"},{"+dtoc(date2)+"})"
query.order = "invert(completeddate)"
ix = tbl.query_create()
parentform.refresh_layout()
2.
dim begin_date as D
dim end_date as D
begin_date = date1
end_date = date2
tbl = table.current()
query.filter = between_date("completeddate",var-"begin_date,var-"end_date)
query.order = "invert(completeddate)"
ix = tbl.query_create()
parentform.refresh_layout()
* I have also tried variations of these scripts that I thought might work with no luck, I even tried hard coding the dates instead of using variable
These scripts both filter the browse correctly except I don't think it is utilizing LQO as per response being slower than with character fields.
Is this normal or have I got it all wrong??
Just an observation when I do a query just based on referencing the completeddate date field it seems to utilize the LQO.
i.e. query.filter="completeddate = begin_date"
Comment