Pull Month to Date Data based on Current Date

[hoodedrobin1]

IIf(DatePart("d",Now())<2,>=DateSerial(Year(Date()-1),Month(Date())-1,1) And <DateSerial(Year(Date()),Month(Date()),1),>=DateSerial(Year(Date()-1),Month(Date()-1),1))

What I would like to do, unless the date is the first of the month pull current month to date data. If it is the first (less than day 2 of the month) it will pull last months data.

However when I write this, I get null values.

-Best Regards

 

[xenou]

Here is an example with IIF().
CDate() isn't necessary here btw but that's how I tested it so I'm leaving it in.

select * from TABLE_1
where 
	CDate(TIME_UPDATED) >= IIf(
				Day(Date())=1,
				DateSerial(year(Date()-1), month(Date()-1), 1),
				DateSerial(year(Date()), month(Date()), 1)
				)
	and CDate(TIME_UPDATED) < IIf(
				Day(Date())=1,
				DateSerial(year(Date()),month(Date()),1),
				DateSerial(year(Date()),month(Date())+1,1)
			)

This example is slightly different from Post 3

BETWEEN logic (appropriate for DATE data or DateTime data converted to Dates):

select * from MyTable where 
	transdate >= IIf(Day(Date()) = 1, FirstDayOfLastMonth, FirstDayOfThisMonth)
	and
	transdate <= IIf(Day(Date()) = 1,LastDayOfLastMonth,LastDayOfThisMonth)

GT/LT logic (appropriate for DateTime data, either dates or dates with times - note that 1/31/2017 23:59:59 is indeed less than 2/1/2017 but 1/31/2017 09:23:31 is not less than 1/31/2017 so it is critical to make a test that includes all the times that may exist on the last day of the month):

select * from MyTable where 
	transdate >= IIf(Day(Date()) = 1, FirstDayOfLastMonth, FirstDayOfThisMonth)
	and
	transdate <= IIf(Day(Date()) = 1,FirstDayOfThisMonth,FirstDayOfNextMonth)