Forum > Database
[SOLVED]DATASET Doesn't Filter as Expected
kito:
Hello
I am working on a program that calculates the resumption date after a certain number of days of suspension, based on the value in the DAYS field.
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---END = START+ DAYS
https://www3.0zz0.com/2023/07/17/19/582840362.png
The resumption date is on the next working day of END.date, excluding weekends
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet); begin if (DayOfWeek( END )=6 )then resumption := IncDay(END,2) else resumption := IncDay(END,1) end;
So far, everything is working fine. However, when I try to filter the days that match the resumption day, the program does not consider the condition and only gives me the next working day after the suspension end date Despite the values in the DATASET being correct.
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Dataset.Filter:='resumption like '+ QuotedStr( DateToStr( DateTimePicker.date)) ; Dataset.Filtered:=True;
https://www3.0zz0.com/2023/07/17/19/892009578.png
Could you please help me with this issue?
LAZARUS 2.2.6 32bit
FPC 3.2.2
OS Win 10 64 bit
alpine:
Not quite sure where the problem is, I can see a few potential pitfalls:
* like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates
* You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)
* You're using a field name (END) which is usually a SQL reserved word, it might be a problem
Why don't you use TDataset.OnFilterRecord to filter programmatically? Even more so because you have calculated fields.
kito:
thank you alpine for your replay
--- Quote ---
* like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates
--- End quote ---
It works pretty fine
--- Quote ---
* You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)
--- End quote ---
it's a data field the calculated field is the number of days between START and End, I don't know if I made a mistake when I have incorporated it into this procedure my goal is to have the resumption date changed automatically once I Change (START, END)
--- Quote ---
* You're using a field name (END) which is usually a SQL reserved word, it might be a problem
--- End quote ---
All field names in the topic are aliases to approximate meaning and save time.
--- Quote ---Why don't you use TDataset.OnFilterRecord to filter programmatically? Even more so because you have calculated fields.
--- End quote ---
In fact, I use many filtering cases , and I fear that using TDataset.OnFilterRecord may limit my options.
Zvoni:
Going out on a Limb:
DateTimePicker.Date is of Type TDate which is an Alias for TDateTime which is an alias for double.
He's using the LIKE-Operator without wildcards which translates to equality.
Now the question is: What DataType is "resumption"? What Database is underneath it all?
He's converting the Date to a string.
My guess is he's running afoul of equality comparisons of float's
kito:
Hello Zvonic
I am using sqlite database, resumption is vchar, I am not facing any problem using the filter expression but it behave strangely when it comes to the date that muchs the weekend. where I can't filter the dataset matching DateTimePicker.Date, but I must select a specific date(end+1 day)in order to see the results(end+2 days) The strange thing is that the date selected in the DateTimePicker is completely different from the results shown in the filtered dataset.
Navigation
[0] Message Index
[#] Next page