CFMX 7 report tool extention for dreamweaver

[Boise-J-Man]

For thos with CFMX7 using the dreamweaver report extension here is the
challenge. 1) write a report that queries a access database using a where
clause that uses a variable as the input where the column datatype is 'Date'.
I can't seem to do it. Any Geniuses out there who are smarter than me please
help!!!! I have tried without success to query a access database using a date
field as a constraint & a variable. example 1 select unitname,
sum(revenue) as revenuetotal, sum(runlength) as runtime where (wr.id='#id#'
and rundate < #startdate#) group by unitname example 2 select unitname,
sum(revenue) as revenuetotal, sum(runlength) as runtime where (wr.id='#id#'
and rundate < #2/1/2005#) group by unitname example 1&amp; 2 will both work in
the test mode, but only example 1 will work in an actuall CFMX7 report. the
date field, can't be passed for some reason as a parameter. the # signs are
required, and even if I embed the # signs in the parameter and pass them, it
won't work, other than when I test the query. I can pass parameters to every
field except a date field. I can query the database using cfml without a
problem, it is just in the report that is driving me nuts!! I am hoping that I
am just overlooking something. Those of you who use the report tool will run
into this sooner or later, help me solve this one now. Thanks, Jeremy Merritt

[danilocelic *TMM*]

Boise-J-Man wrote:

> For thos with CFMX7 using the dreamweaver report extension here is the
> challenge.

What report extension is that? I don't see one listed:
[url]http://www.macromedia.com/software/coldfusion/productinfo/features/static_tour/development/dw_ext.html[/url]


Do you perhaps mean the report Builder application?:
[url]http://www.macromedia.com/software/coldfusion/reporting/[/url]

If not, which one are you talking about?

> 1) write a report that queries a access database using a where

Dates in Access require a # around them through SQL, so with CF which takes # symbols as an escape character for variable values, you'd need to use something like the following:
###startdate###


--
Danilo Celic
| Extending Knowledge Daily : [url]http://CommunityMX.com/[/url]
| Team Macromedia for Dreamweaver : [url]http://macromedia.com/go/team/[/url]

[Boise-J-Man]

Thanks! My complete query is as follows SELECT UnitName, sum(Revenue) as
rev, sum(RunTimeTotal) as rtt FROM AllActivity where ((id='#ID#') and
((datestamp > ###startdate###) and (datestamp <
###enddate###))) group by unitname ORDER BY UnitName Note the ###'s I
wasn't able to make the report function while the query was embedded in the
report. Once I removed it and used the above query in the .cfm file it worked.
Thanks. Jeremy Merritt