How do I prompt for a date range in report?

[JohnKlassen]

Hi,

I have just created a report that displays all of the rows where vio_date_entered is within a date range.

Code: Select all

$sql = "CREATE TABLE #dataTable# SELECT * FROM violations ";
$sql .= "WHERE vio_date_entered > '2012-10-01' AND vio_date_entered < '2012-11-01' ";
$sql .= "ORDER BY vio_date_entered ";
nuRunQuery($sql);

How do I prompt the user for the beginning and ending date in the date range, ideally using a calendar? Is there a sample I could look at?

Thanks,

John

[johan]

John,


You first have to create a new form (fill in name = date select - title = select -table = date - primary key = date and used for report = yes)
Add 2 textfields vfrom and vto (format 13-01-2007).You can leave all the other fields of form blank
Create new report with selection creteria form = date select
In report your code will be something like

Code: Select all

   
$from         =  reformatField('#vfrom#',11,$quotes = false);
$to         =   reformatField('#vto#', 11,$quotes = false);

$sql = "CREATE TABLE #dataTable# SELECT * FROM violations ";
    $sql .= "WHERE vio_date_entered > '$from' AND vio_date_entered < '$to' ";
    $sql .= "ORDER BY vio_date_entered ";
    nuRunQuery($sql);

look in wiki for other formats.

Hope this helps.
Johan

[JohnKlassen]

Johan,

Thanks very much. I will try that.

John

[admin]

.