Problem passing variables...

[ndeeley]

Hello,

Trying to export the results of a query into a spreadsheet, which I can do, but not as I like.

Procedure is this:

Form holds 2 date fields - ToDate, FromDate

query_CreateSho wTable.cfm shows the results as a table with all completed records within that date range. On the web page is a button with 'Export to Excel' on it. This exports the results to XLS format using the action query query_ExportToE xcel.cfm

I'm exporting to XLS by running this query, but it doesn't work as the query requires the values of ToDate & FromDate from my form.

How can I pass the values of ToDate & FromDate to the query _ExportToExcel. cfm?

Thanks
Neil

[acoder]

You can use something like:

exportToExcel.cfm?FromDate=#FromDate#&ToDate=#ToDate#

[ndeeley]

Hi acoder,

I've tried that, but it doesn't seem to be accepting the value of the ToDate.

Here's the links action:

<a href="action_CreateExcelReport.cfm?DateFrom=#DateFrom#&DateTo=#DateTo#" title="Export report to Excel .csv format" onMouseOver="window.status='Export report to Excel .csv format';return true;"><img src="assets/excel.png" /></a>

and here's the handling script of the action_CreateEx celReport.cfm:

<cfquery datasource="taskbook" name="JobReport">
select 		ID,
			CustomerName,
			Dept,
			Location,
			ContactNo,
			EmailAddress,
			ChargeCode,
			RequestDate,
			TaskTypeFK,
			TaskDetails,
			JobStatusFK
from		tblTaskBooker	
where		ChargeableYN = 1 and JobStatusFK like 'Completed'
			and ((RequestDate BETWEEN #DateFrom# AND #DateTo#) or (RequestDate Is Null))
order by	CompletionDate
</cfquery>

Anything I am doing wrong here?

[acoder]

Yes, when passed to the page via the URL, they can be accessed as #url.DateFrom# and #url.DateTo#.

[ndeeley]

Thanks, I have changed that, but now I`m getting this error:

Parameter 1 of function CreateODBCDate which is now "" must be a date/time value

It's not passing the value for some reason.I've tried outputting it at the top of the page but its empty.

[acoder]

What's the value in the URL? Are you seeing "action_CreateE xcelReport.cfm? DateFrom=&DateT o=" in the address bar? If so, you need to check the values before they're passed on the CreateShowTable .cfm page.

[ndeeley]

Sort of...what I am seeing is this:

http://marlin/assetinl/scripts/TaskBook/action_CreateExcelReport.cfm?DateFrom=#fdate&DateTo=tdate

I'm creating two variables to hold the dates:

<cfset fdate = '#form.QuickRpts_DateFrom#'>
<cfset tdate = '#form.QuickRpts_DateTo#'>

Then passing those variables along the url string:

<a href="action_CreateExcelReport.cfm?DateFrom=#fdate#&DateTo=#tdate#" title="Export report to Excel .csv format" onMouseOver="window.status='Export report to Excel .csv format';return true;"><img src="assets/excel.png" /></a>

and I have outputted the values at the bottom of the page (ie the one with the export to excel button on it) and it is showing the correct date values:

<cfoutput>
#fdate# <br />
#tdate#
</cfoutput>

of
01/09/2010
01/10/2010

so I have no idea where I am going wrong!
Thanks for your help!
Neil

[acoder]

Ah right. The URL should of course be in <cfoutput> tags otherwise it'll just be a string #fdate# instead of the actual value of the variable.

[ndeeley]

Brilliant - got it all to work!

Thanks for all your help.

Cheers
Neil

[acoder]

No problem. Glad to :)