Skip to main content
J
jlig
Inspiring
May 7, 2011
Question

Weird Date Format being passed in my Report URL?

  • May 7, 2011
  • 1 reply
  • 925 views

I have a simple cfm page with three Form elements: startDate, endDate, and a Spry Menu with links to my seven reports.

report prompts.jpg

I have added "Parameters" for the form.startDate & form.endDate and passing them to the Reports in my Spry menu dropdown.

- Here is a sample Report URL after clicking a report in my Spry drop-down:

http://192.168.1.77/DailySalesReport/reports/01_DSR_Mgt_Report_by_Sales.cfr?startDate={ts '2011-05-06 00:00:00'}&endDate={ts '2011-05-06 00:00:00'}

- What is causing the weird Start & End Dates that have the "{ts" name & brackets?

- Why are my Dates typed in above on my Form page not being forwarded in the Report URL?

Note: In testing, If I use a normal Submit button on the page (see Daily Sales button below), to run a report, it works fine and the parameters are passed to the report and the report prints just the data for April.

- Here is the Full Code listing for the page: http://cerberus.clearwave.com/jerry/SalesHTML.txt

http://forums.adobe.com/servlet/JiveServlet/showImage/2-3650969-67272/report prompts.jpg

- I'm using DW CS4 & CF 7.02

- All of my Reports are created in Cold Fusion Report Builder using Parameters like this:

- WHERE tblSalesCatg.scID IN ('1', '2', '3', '4','5', '6', '7','8', '9', '10') AND DATE_FORMAT(tblSalesReport.srCompDate, '%m/%d/%Y') BETWEEN '#param.startDate#' AND '#param.endDate#'

This topic has been closed for replies.

1 reply

J
jligAuthor
Inspiring
May 7, 2011

By the way, the error I get on my webpage is:

Error Executing Database Query.

Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2011-05-06 00:00:00'}' AND '{ts '2011-05-06 00:00:00'}' GROUP BY tblLocation.' at line 19
The error occurred in C:\Inetpub\wwwroot\DailySalesReport\reports\01_DSR_Mgt_Report_by_Sales.cfr: line 1151

...WHERE tblSalesCatg.scID IN ('1', '2', '3', '4','5', '6', '7','8', '9', '10')

AND DATE_FORMAT(tblSalesReport.srCompDate, '%m/%d/%Y')

BETWEEN '{ts '2011-05-06 00:00:00'}' AND '{ts '2011-05-06 00:00:00'}'

GROUP BY tblLocation.location, tblCCReps.CCRepName

ORDER BY tblLocation.location, tblCCReps.CCRepName

J
jligAuthor
Inspiring
May 9, 2011

If I "manually" add the dates to my report URL, the report filters properly and runs..

http://192.168.1.77/DailySalesReport/reports/01_DSR_Mgt_Report_by_Sales.cfr?startDate=04/01/2011&endDate=04/30/2011

So, this seems to be an issue with my report link unable to "grab" the "form.startDate" and form.endDate" on my main page/form and "Pass" them to the report URL?

Anyone have any ideas..?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices