Filtering by Month

  • Thread starter Thread starter Access Newbie Nick
  • Start date Start date
A

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
 
'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]...
 
Back
Top