Problem with SQL Syntax

[tommygill]

Hi All

This forum has been most helpful so I will post another small problem.

The following code, which contains SQL statements, works great.

Code: Select all

globals.current_donation_date = donation_date;
   //This query will return the records for this report
   var query = "SELECT DISTINCT detail_query_totals FROM donation_detail WHERE detail_date = ?";
   //This query will return the PK's of the records for this report
   var query_pk = "SELECT donation_detail_id FROM donation_detail WHERE donation_detail_id IN " + "(SELECT MIN (donation_detail_id) FROM donation_detail WHERE detail_date = ? GROUP BY detail_query_totals)" + "ORDER BY detail_query_totals asc";
   var args = new Array();
   args[0] = utils.dateFormat(donation_date, 'MM/dd/yyyy');
   var dataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query,args,-1);
   var ds_pk = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()),query_pk,args,-1);
   var recCount = dataset.getMaxRowIndex();
   //Load the desired records into a form's foundset using the PK's from ds_pk
   forms.Report_Current_Receipt_Totals.controller.loadRecords(ds_pk);
   //put some code here to do what you want with dataset
   forms.Report_Current_Receipt_Totals.controller.showPrintPreview();

As you can see, both statements us the args array which contains one item, a date. I would like to modify the statement to retrieve all the records where the date is between a start date and an end date. Can this be done using the args array or do I need to recode a different way?

Thanks for all your help.

[ROCLASI]

Hi Tom,

You can use the following syntax:

Code: Select all

SELECT * FROM myTable WHERE myDate BETWEEN ? AND ?

This way you simply pass the start and end date.

Hope this helps.

[tommygill]

Thanks For the Help. It has taken a lot of time to get back to you because the debug feature stopped working, which is not your fault. Your suggestion of BETWEEN & AND was quite successful. Thanks so much.