Topic: Need Help

Hi friends,
              I am facing a problem. Any one who guide me? Problem is:
I have made a project. Suppose it has a form1, a editbox two date time pickers and a table name Sale having svalue currency field, a date sdate field and a item text field. I want to get svalue on editbox. I use this statement which works very will
procedure form1_OnShow (Sender: TObject; Action: string);
begin
              form1.edit1.Text:= SQLExecute('Select Sum(svalue) from Sale');
end;
but I want to get svalue for specific period or date. How can I write statement which will give my required result.
Thank in advance.

2 (edited by tcoton 2020-07-29 18:26:58)

Re: Need Help

How about this?

form1.edit1.Text:= SQLExecute('Select Sum(svalue) from Sale where sdate between {datetimepicker1} and {datetimepicker2}';

Re: Need Help

It give an error message
near "{": syntax error
Script:
SQLExecute

Re: Need Help

I am trying to use following statement but it also giving above error message.

form1.Edit1.Text:= SQLExecute('Select Sum(svalue) from Sale Where' + 'Date(Date1) >= "' + FormatDateTime('dd-mm-yyyy',Sale.datetimepicker1.DateTime)
       + '" and Date(Date1) <= "' + FormatDateTime('dd-mm-yyyy',Sale.datetimepicker2.DateTime));

Re: Need Help

unforgettable,
Dates in databases are stored in the YYYY-MM-DD format so you need to reformat your datetimepickers in that format.


form1.Edit1.Text:= SQLExecute('Select Sum(svalue) from Sale Where Date(Date1) >= "' + FormatDateTime('YYYY-MM-DD',Sale.datetimepicker1.DateTime) + '" and Date(Date1) <= "' + FormatDateTime('YYY-MM-DD'',Sale.datetimepicker2.DateTime) + '"');

Re: Need Help

hi ehwagner
            Thank to reply,  I put your typed statement but now cursor blink on last part of statement  '"'); It is not working.
Last semi coln ; is blue not red means there is error

7 (edited by blackpearl8534 2020-07-30 15:29:32)

Re: Need Help

check it out

form1.edit1.Text:= SQLExecute('Select Sum(svalue) from Sale where sdate >= '+form1.datetimepicker1.sqldatetime+' and sdate <='+form1.datetimepicker2.sqldatetime+' ');

if not works then change
sqldatetime to datetime

Re: Need Help

My apology. I see two errors in my statement. Change the second formatter to 'YYYY-MM-DD'. I was missing the 4th Y and it should be single quotes around it.

9 (edited by tcoton 2020-07-31 22:45:50)

Re: Need Help

The difficulty is to select a date range before even loading the form, so I guess, you were looking for a snippet to implement within existing forms.

I spent some time trying to get something working and it works if you add some components to your form, without your project, we can only give you hints.

I added two components:

- one label to display the result - could be an Edit box too
- one button to trigger the search with no action

Here is the working script (tested multiple times with success):

procedure Form1_Button2_OnClick (Sender: TObject; var Cancel: boolean);

begin

  form1.Label1.Caption:= SQLExecute('Select Sum(svalue) from Sale where sdate between '+form1.DateTimePicker1.sqlDate+' and '+form1.DateTimePicker2.sqlDate+'');
end;

Edited the first sentence to sound more positive.

Re: Need Help

Hi ehwager,
                   Thank you to guide. Now it work perfectly. Would you like to explain statement?
Can we use two ands for example where id = 25 and Date(date1).....

Re: Need Help

Yes, you can add multiple "and" after a "where" but you might also need to use a "or" if the results are not what you expected.

Re: Need Help

Thanks to all to guide.

Re: Need Help

The authoritative message smile