date function in the criteria access 2007 query?

Question

I'm trying to use a date function in the criteria filed of my access query.

The function in the criteria field is >=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

This function actually finds the 1st date of the current month in date format.

Access says that the formula contains wrong no of arguments

Answer 1

Favorite Answer

Access date functions work differently from Excel. In Access, DATE() is the same as TODAY() in Excel. It takes no arguments within the brackets. Access has no function TODAY().

I assume you are trying to find all values greater than or equal to the First of the current month. If you are in USA this would be

>=DateValue(Month(Date()) & "/" & "01/" & Year(Date()))

If you are in the UK this would be

>=DateValue("01/" & Month(Date()) & "/" & Year(Date()))

Answer 2

you are able to not use IIF to sparkling up this ( properly, you are able to attempt nested IIFs), attempt the swap function instead: swap( [DaysSinceRecd] >= -5 And [DaysSinceRecd] <=10, "0" , [DaysSinceRecd] >= 11 And [DaysSinceRecd] <= 25 , "15" , [DaysSinceRecd] >= 26 And [DaysSinceRecd] <= 40 , "30" , [DaysSinceRecd] >= 40-one And [DaysSinceRecd] <= fifty 5 , "40 5" , [DaysSinceRecd] >= fifty six And [DaysSinceRecd] <= 70 , "60" , [DaysSinceRecd] >= seventy one And [DaysSinceRecd] <= 80 5 , "seventy 5" , [DaysSinceRecd] >= 86 And [DaysSinceRecd] <= one hundred , "ninety" , [DaysSinceRecd] >= one hundred and one And [DaysSinceRecd] <= a hundred and fifteen , "one 0 five" , [DaysSinceRecd] >= 116 And [DaysSinceRecd] <= one hundred thirty , "a hundred and twenty" , [DaysSinceRecd] >= 131 And [DaysSinceRecd] <= a hundred forty five , "a hundred thirty five" , [DaysSinceRecd] >= 146 And [DaysSinceRecd] <= one hundred sixty , "a hundred and fifty" , [DaysSinceRecd] >= 161 And [DaysSinceRecd] <= a hundred seventy five , "a hundred sixty five" , [DaysSinceRecd] >= 176 And [DaysSinceRecd] <= a hundred ninety , "a hundred and eighty" , [DaysSinceRecd] >= 191 And [DaysSinceRecd] <= 205 , "195" , [DaysSinceRecd] >= 206 And [DaysSinceRecd] <= 220 , "210" , [DaysSinceRecd] >= 221 And [DaysSinceRecd] <= 235 , "225" ) presently, i've got not got get entry to to MS-get entry to so would desire to not attempt this myself yet supply it a attempt. Zafar