Problem with SQL Syntax

Classic Servoy
1

Hi All

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

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

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.

2

Hi Tom,

You can use the following syntax:

SELECT * FROM myTable WHERE myDate BETWEEN ? AND ?

This way you simply pass the start and end date.

Hope this helps.

3

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.