Problem with SQL statement

[wezcornell2008]

Hi Guys

For some reason the following SQL statement will not work in CF Report Builder 8, it seems to be with the date part:

SELECT transactions.ourRef, transactions.transDate,
IIf(transactions.transTypeID = 2, transactionDetails.goodsVatable * -1, transactionDetails.goodsVatable) AS goodsVatable,
IIf(transactions.transTypeID = 2, transactionDetails.goodsNonVat * -1, transactionDetails.goodsNonVat) AS goodsNonVat,
IIf(transactions.transTypeID = 2, transactionDetails.Vat * -1, transactionDetails.Vat) AS vat,
IIf(transactions.transTypeID = 2, transactionDetails.grandTotal * -1, transactionDetails.grandTotal) AS grandTotal, clients.clientCode, transTypes.transTypeDesc
FROM transactions, transactionDetails, clients, transTypes
WHERE transactions.transID = transactionDetails.transID
AND transactions.clientID = clients.clientID
AND transactions.transTypeID = transTypes.transTypeID
AND (transactions.transDate BETWEEN #param.dateFrom# AND #param.dateTo#)

Many thanks in advance

Wes

[Guest]

You of course have tried this same EXACT query in a regular .cfm file, correct? Passing in the param.dateFrom and param.dateTo in the same EXACT way.

Perhaps the dates need to be in single quotes?

[BKBK]

What do you mean when you say it will not work? Do you get an error or does the query give a result you didn't expect? As Scottcook has suggested, test the query by putting the actual date values in, and running it elsewhere.