I'm stumped on a query filter I'm trying to put together. Here's what I need it to do:
1. Run a query that populates a table with order information to be gathered for regular invoices.
2. A user can filter date ranges, location, client and payment status. The options in the state and client field are derived from the main query.
3. Multiple entries can be selected from the client and state fields.
4. The form handler assembles a WHERE statement from the form values passed over and inserts it, as a local variable, into a QoQ.
5. The output from the QoQ repopulates the table.
There are two areas where I'm running into trouble, in assembling the WHERE statement:
First: the date range. I have a From date, that defaults at 9/1/2011, around the time orders started being placed, and a To date, which defaults to Now(). I declared the variables containing them like so:
<cfparam name="fromdate" default="" type="date">
I then set it to the appropriate form value like so:
<cfset fromdate = DateFormat(#form.from_date#, 'yyyy-mm-dd')>
Same with the To date form value. Remember, the default value is Now().
I run a QoQ with a WHERE statement of
paid_date between #fromdate# AND #todate#
And I get this error:
Invalid parameter type. The value cannot be converted to a date because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values.
When I did this without setting a format or data cast, it reads the From date as a double and the To date as a date. I even went into my form and formatted the default values for each using CreateDate(). I thinkered with this any number of ways, but I still get a datatype mismatch. Even when I use values that are not the default values, I get the same thing. As for the form sending these over, the <cfinput> attributes for each are identical, except for the field name and default values.
The other issue I am having is in assembling a longer WHERE statement using an IN operator. The local variables containing the form values passed over can contain a list, as I've allowed multiple selections. For each field there is a variable
where_n. If a list is passed over, it is set to
where_n = ' AND field IN ('#form.field#')'. If all values are being searched for that field, it is set to
''. A series of variables containing the list, or nothing if the default (all values) is passed over. The final expression putting it all together looks like this:
<cfset search_string = 'paid_date between #fromdate# AND #todate#'&#where_1#&#where_2#&#where_3#>
Here's the problem: the WHERE statement generated drops an extra single quote where each appears in the expression. So, instead of:
(paid_date between '2011-09-01' AND '2012-04-01') AND client IN ('client1,client2,client3')
(paid_date between ''2011-09-01'' AND ''2012-04-01'') AND client IN (''client,client1,client2,client3'')
Which, of course, doesn't work. I tried tinkering with the placement of quotes in the tags setting the variables, but it's either two single quotes, or no quotes, which also doesn't work (I'm using PostgreSQL).
When I run this in my PostgreSQL Admin app, it works OK with the single quotes I expect, though it doesn't want to seem to find the field values from the list.
I apologize for such a lengthy post, which touches on multiple issues. The data mismatch is the biggest problem right now, I can always come back to the multiple selection and search parameters at a later time.