I am trying to create a select query in EWB that uses a date in the WHERE clause.  I am using EDB database.

SELECT * FROM incidents WHERE incident_date={incident_date=2014-10-23}

I get the error "Expected date expression but found ''...

How should this be written?

Will EDB accept a date written as mm/dd//yyyy ?  

Thanks,
TD

Hi TD,

> SELECT * FROM incidents WHERE incident_date={incident_date=2014-10-23}

SELECT * FROM incidents WHERE incident_date= DATE '2014-10-23'


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

"Terry Swiers - Millennium Software, Inc." wrote:

Hi TD,

> SELECT * FROM incidents WHERE incident_date={incident_date=2014-10-23}

SELECT * FROM incidents WHERE incident_date= DATE '2014-10-23'

I am new to EDB and EWB.  I am trying to create a EWB dataset that uses a query as the row source.  I want to be able to filter the results using a date range.  What is the proper syntax to accomplish this?

Also, the dataset is the data source for a grid on the main form so there will not be any values in Edit /Edit2 when the form loads.  How can i handle that?  

My Query:

SELECT * FROM incidents WHERE (incident_date >= DATE{Form1.Edit1.Text}) AND (incident_date <= DATE{Form1.Edit2.Text})

Thanks,
TD

TD


Second part of your question

>SELECT * FROM incidents WHERE (incident_date >= DATE{Form1.Edit1.Text}) AND (incident_date <= DATE{Form1.Edit2.Text})

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE{Form1.Edit1.Text} AND DATE{Form1.Edit2.Text}

but make sure that the date in Edit2 is greater than the data in Edit1. ElevateDB is currently sensitive about that and if the other way round will not return anything


>Also, the dataset is the data source for a grid on the main form so there will not be any values in Edit /Edit2 when the form loads. How can i handle that?

Depends what you want to show in those circumstances. If nothing simply don't run the query.

Roy Lambert

Roy Lambert wrote:

TD


Second part of your question

>SELECT * FROM incidents WHERE (incident_date >= DATE{Form1.Edit1.Text}) AND (incident_date <= DATE{Form1.Edit2.Text})

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE{Form1.Edit1.Text} AND DATE{Form1.Edit2.Text}

but make sure that the date in Edit2 is greater than the data in Edit1. ElevateDB is currently sensitive about that and if the other way round will not return anything


>Also, the dataset is the data source for a grid on the main form so there will not be any values in Edit /Edit2 when the form loads. How can i handle that?

Depends what you want to show in those circumstances. If nothing simply don't run the query.

Roy Lambert

Thanks for helping Roy but when I tried your suggestion I get the error shown in the attached screen capture.  BTW, I got the same error when I tried the way I posted.  

Hi TD,

> SELECT * FROM incidents WHERE incident_date  BETWEEN
> DATE{Form1.Edit1.Text} AND DATE{Form1.Edit2.Text}

Wrong format.  If you are passing the date as a string, it has to be
enclosed within single quotes and in YYYY-MM-DD format.  So the above query
should be

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
'{Form1.Edit1.Text}' AND DATE '{Form1.Edit2.Text}'

And don't forget that you have to replace {Form1.Edit1.Text} and
{Form1.Edit2.Text}, including the braces, with the actual date.


If the user is going to be changing the dates and refreshing the results,
why not use paramaters.   Create the TEDBQuery object (named EDBQuery1) and
drop the following query in the SQL property:

SELECT * FROM incidents WHERE incident_date  BETWEEN :BegDate AND :EndDate

In the BeforeOpen event of the query object, add the following code:

begin
EDBQuery1.ParamByName('BegDate').AsDate := StrToDate(Form1.Edit1.Text);
EDBQuery1.ParamByName('EndDate').AsDate := StrToDate(Form1.Edit2.Text);
end;

This will make sure that every time the query is opened, it uses the current
values of the edit fields.  To refresh the query results if the date range
changes, just close and reopen the query.  And to speed things up a bit, you
can prepare the query after you load the form so it doesn't have to go
through the prepare functionality every time you change the date range.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

"Terry Swiers - Millennium Software, Inc." wrote:

Hi TD,

> SELECT * FROM incidents WHERE incident_date  BETWEEN
> DATE{Form1.Edit1.Text} AND DATE{Form1.Edit2.Text}

Wrong format.  If you are passing the date as a string, it has to be
enclosed within single quotes and in YYYY-MM-DD format.  So the above query
should be

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
'{Form1.Edit1.Text}' AND DATE '{Form1.Edit2.Text}'

And don't forget that you have to replace {Form1.Edit1.Text} and
{Form1.Edit2.Text}, including the braces, with the actual date.


If the user is going to be changing the dates and refreshing the results,
why not use paramaters.   Create the TEDBQuery object (named EDBQuery1) and
drop the following query in the SQL property:

SELECT * FROM incidents WHERE incident_date  BETWEEN :BegDate AND :EndDate

In the BeforeOpen event of the query object, add the following code:

begin
EDBQuery1.ParamByName('BegDate').AsDate := StrToDate(Form1.Edit1.Text);
EDBQuery1.ParamByName('EndDate').AsDate := StrToDate(Form1.Edit2.Text);
end;

This will make sure that every time the query is opened, it uses the current
values of the edit fields.  To refresh the query results if the date range
changes, just close and reopen the query.  And to speed things up a bit, you
can prepare the query after you load the form so it doesn't have to go
through the prepare functionality every time you change the date range.

Terry, my bad.  This query is in the dataset creation part of Elevate Web Builder.  Should I have posted this question there instead of here?

TD

>Terry, my bad. This query is in the dataset creation part of Elevate Web Builder. Should I have posted this question there instead of here?

There's no harm in posting database questions here, but if they're about how to do something with EWB then its probably best posted there. Some people can help with both, others with only one. eg  I've downloaded EWB but have yet to use it.

Roy Lambert

> Terry, my bad.  This query is in the dataset creation part of Elevate Web Builder.

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
'{Form1.Edit1.Text}' AND DATE '{Form1.Edit2.Text}'

An EWB dataset should look like this:

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
{datestart=''} AND DATE {datestop=''}

You have to load the dataset with these params:

MyDataset.params.clear;
MyDataset.params.add('datestart=''' + sDateStart + '''');
MyDataset.params.add('datestop=''' + sDateStop + '''');
Database.load(MyDataset);

sDateStart and SDateStop have to be formatted like this:

'2014-10-01' for 1st of October 2014 e.g.

Uli

Uli Becker wrote:

> Terry, my bad.  This query is in the dataset creation part of Elevate Web Builder.

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
'{Form1.Edit1.Text}' AND DATE '{Form1.Edit2.Text}'

An EWB dataset should look like this:

SELECT * FROM incidents WHERE incident_date  BETWEEN DATE
{datestart=''} AND DATE {datestop=''}

You have to load the dataset with these params:

MyDataset.params.clear;
MyDataset.params.add('datestart=''' + sDateStart + '''');
MyDataset.params.add('datestop=''' + sDateStop + '''');
Database.load(MyDataset);

sDateStart and SDateStop have to be formatted like this:

'2014-10-01' for 1st of October 2014 e.g.

Uli

Thank you, I'll try that and report back,
TD