problems with datepart in access

[grabit]

Hi Peoples
I am having probs with the datepart function with a query.What i want is to get all listings made in any month in any year ie May 2007
i have 2 drop down boxes to select the listedmonth and listedyear on the refering page
I need to be able to retrieve this so i can send out renewals to those listings for another year.
I am using this query
<cfquery name="getrenew" datasource="#dsn#">
SELECT datelisted
FROM listings
WHERE datepart('yyyy', datelisted) =#form.listedyear#
</cfquery>
That gets the year ok but if i try and add an AND datepart('mm', datelisted) =#form.listedmonth# to the where statement i get the following error

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'datepart('yyyy', datelisted) =2007 AND datepart('mm' datelisted)=07'.

How can i get this to work please
cheers and thanks in advance
Grabit

[nico5038]

I would use the Year() and Month() function to achieve this like:

WHERE Year(datelisted) = " & form.listedyear & " and Month(detelisted) = " & form.listedmonth
 

Getting the idea?

Nic;o)

[grabit]

I would use the Year() and Month() function to achieve this like:

WHERE Year(datelisted) = " & form.listedyear & " and Month(detelisted) = " & form.listedmonth
 

Getting the idea?

Nic;o)

Thanks heaps Nic i had to make a few changes for the coldfusion type code but this works perfectly.
WHERE Year(datelisted) = #form.listedyear# and Month(datelisted) = #form.listedmonth#
Thanks very much

[nico5038]

Glad I could help, success with your application !

Nic;o)