CF Error when using DateFormat In Access 1

[profwannabe]

Can't seem to figure this one out. I need to select all records in the table based on a user-input year; the date/time field DateReceived contains a full ODBC date. Oddly enough, I get a "too few parameters" error through my CF template, but the query works fine in Access (when I provide values for the cf parameters). My debugging code shows the parameters are being successfully passed with the expected values. Also, the error only occurs when I include the WHERE clause:
AND DatePart("YYYY", DateReceived) = #attributes.GiftingYear#

Any ideas on this one? :-I
Below is the query code from the cf template.


SELECT SUM(Levels.LevelDescription) AS AnnualGiftTotal, Members.MemberID, Members.MemberLastName, Members.MemberFirstName
FROM (Gifts INNER JOIN (Relationships INNER JOIN Levels ON Levels.LevelID = Relationships.LevelID)ON Relationships.RelationshipID = Gifts.RelationshipID) INNER JOIN Members ON Members.MemberID = Gifts.GifteeID
WHERE GiftorID = #attributes.MemberID#
AND DatePart("YYYY", DateReceived) = #attributes.GiftingYear#
GROUP BY Members.MemberID, Members.MemberLastName, Members.MemberFirstName
ORDER BY Members.MemberLastName, Members.MemberFirstName

 

[tleish]

Change the double quotes in DatePart("YYYY", DateReceived) to single quotes DatePart('YYYY', DateReceived)

=== START CODE EXAMPLE ===
<CFQUERY...>
SELECT SUM(Levels.LevelDescription) AS AnnualGiftTotal, Members.MemberID, Members.MemberLastName, Members.MemberFirstName
FROM (Gifts INNER JOIN (Relationships INNER JOIN Levels ON Levels.LevelID = Relationships.LevelID)ON Relationships.RelationshipID = Gifts.RelationshipID) INNER JOIN Members ON Members.MemberID = Gifts.GifteeID
WHERE GiftorID = #attributes.MemberID#
AND DatePart('YYYY', DateReceived) = #attributes.GiftingYear#
GROUP BY Members.MemberID, Members.MemberLastName, Members.MemberFirstName
ORDER BY Members.MemberLastName, Members.MemberFirstName
</CFQUERY>
=== END CODE EXAMPLE ===

or you could also use the SQL function Year() instead

=== START CODE EXAMPLE ===
<CFQUERY...>
SELECT SUM(Levels.LevelDescription) AS AnnualGiftTotal, Members.MemberID, Members.MemberLastName, Members.MemberFirstName
FROM (Gifts INNER JOIN (Relationships INNER JOIN Levels ON Levels.LevelID = Relationships.LevelID)ON Relationships.RelationshipID = Gifts.RelationshipID) INNER JOIN Members ON Members.MemberID = Gifts.GifteeID
WHERE GiftorID = #attributes.MemberID#
AND Year(DateReceived) = #attributes.GiftingYear#
GROUP BY Members.MemberID, Members.MemberLastName, Members.MemberFirstName
ORDER BY Members.MemberLastName, Members.MemberFirstName
</CFQUERY>
=== END CODE EXAMPLE === - tleish

 

[profwannabe]

Worked like a charm!

Just for my own edification, why did the double quotes work in Access but cause the problem when passed in the template, since I am using Access functionality in the template?

Again, thanks a lot!