Filtering by Month

[Access Newbie Nick]

I have a query that i can filter by month with input like 'December 2008'.
The end of the query is as follows,

HAVING (((MonthName(DatePart("m",[income del note].[Delivery Date])) & " " &
Format$([income del note].[Delivery Date],"yyyy"))=IIf(IsNull([Enter Month
and Year (Name_YYYY)]),MonthName(DatePart("m",[income del note].[Delivery
Date])) & " " & Format$([income del note].[Delivery Date],"yyyy"),[Enter
Month and Year (Name_YYYY)])));

However when i try to use a similar one in another query

WHERE (((MonthName(DatePart("m",[Invoice Date])) & " " & Format$([Invoice
Date],"yyyy"))=IIf(IsNull([Enter Month and Year
(Name_YYYY)]),MonthName(DatePart("m",[Invoice Date])) & " " &
Format$([Invoice Date],"yyyy"),[Enter Month and Year (Name_YYYY)])));

I get the message 'this expression is typed incorrectly, or is too complex
to be evaluated'.

Could anyone give an explanation into why this is happening?

Thank you in advance

 

[Allen Browne]

'Too complex' can be triggered by confusing JET about the data types.

Try:
WHERE [InvoiceDate] >= [First Date]
AND [InvoiceDate] < DateAdd("m", 1, [First Date])
Then choose Parameters on the Query menu.
Access opens a dialog. Enter this row:
[First Date] Date/Time

This defines the data type (i.e. user must enter a valid date for First
Date), and returns the values from that date to one month later. As well as
avoiding the error you received, this is much more efficient: JET can use
any index on the Invoice Date field. The expression is crafted so that a
value such as 6:00am Jan 31 2009 is included. (It would not with the Between
operator.)

Alternative:
WHERE [InvoiceDate] >= DateSerial([What Year?], [What Month Number?], 1)
AND [InvoiceDate] < DateSerial([What Year?], [What Month Number?]+1, 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...