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

 

[hoodedrobin1]

How do you edit a post?

Also the query that I was trying to write is actually.


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))

 

[xenou]

I think you are trying to do this:

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

You just need to fill in the logic to get FirstDay...LastDay dates, which I haven't got at hand right now. Notice that you need TWO IIFs here.

When I had this kind of thing going on reports I had single table-based values for "CurrentFiscalYear" and CurrentFiscalPeriod". My reports always used those dates. So when I was ready to flip the switch on reports (i.e, on the evening or morning of day 1 or 2, I just update the table.

 

[hoodedrobin1]

I believe I copied it wrong...


IIf(DatePart("d",Now())<2,>=DateSerial(Year(Date()-1),Month(Date())-1,1) And <dateserial(year(date()),month(date()),1), style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">=DateSerial(Year(Date()-1),>=DateSerial(Year(Date()-1),Month(Date()-1),1))


It does have three parts

IIf(DatePart("d",Now())<2, ---- If the day is less than 2 ie the first of the month-----

If True Return

>=DateSerial(Year(Date()-1),Month(Date())-1,1) And <dateserial(year(date()),month(date()),1),>=DateSerial(Year(Date()-1)

If False Return

>=DateSerial(Year(Date()-1),Month(Date()-1),1)



Why can you edit on this forum???



-Best Regards</dateserial(year(date()),month(date()),1),></dateserial(year(date()),month(date()),1),>

 

[xenou]

If True Return

>=DateSerial(Year(Date()-1),Month(Date())-1,1) And =DateSerial(Year(Date()-1)

What do you expect this return value to actually be (given a date like 2017-11-01 or 2017-11-02)?

Why can you edit on this forum???

You can edit for about 10 minutes after you post.

 

[xenou]

Note that as written this expression does precisely nothing:

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

To be more helpful it might do to see the rest of the code (not even sure if you are writing SQL or putting something in a control or into some kind of vba code - i.e., this is just a "part" of some thing like a sql command or other construction).

 

[hoodedrobin1]

One Query To Do Everything

</pre>SELECT TABLE_1.ID_NUMBER ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) AS [Month] ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED])) AS [date]INTO MTD_OR_PMTDFROM TABLE_1WHERE ( ( (TABLE_1.TIME_UPDATED) = IIf(DatePart("d", Now()) < 2, (TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE ()) - 1, 1) AND (TABLE_1.TIME_UPDATED) < DateSerial(Year(DATE ()), Month(DATE ()), 1), (TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE () - 1), 1)) ) )GROUP BY TABLE_1.ID_NUMBER ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED]));

Currently used for finding Previous Month

SELECT TABLE_1.ID_NUMBER ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) AS [Month] ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED])) AS [date]INTO MTD_OR_PMTDFROM TABLE_1WHERE ( ( (TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE ()) - 1, 1) AND (TABLE_1.TIME_UPDATED) < DateSerial(Year(DATE ()), Month(DATE ()), 1) ) )GROUP BY TABLE_1.ID_NUMBER ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) ,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED]));

Currently used for finding Current Month

SELECT TABLE_1.ID_NUMBER
,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) AS [Month]
,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED])) AS [date]
INTO MTD_OR_PMTD
FROM TABLE_1
WHERE (((TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE () - 1), 1)))
GROUP BY TABLE_1.ID_NUMBER
,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1)
,DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED]));

 

[hoodedrobin1]

nevermind... Does anyone have any ideas?

 

[xenou]

Your IIF is wrong because it doesn't evaluate to a date. I'm not sure what it evaluates to ... maybe "True" or "False" ... but that won't help you. The pattern I gave you in Post 3 looks like what you want to do. Have you tried to implement that structure?

 

[xenou]

This would also work. You basically just run both queries but use your "what day is it" criteria to cause the first one to return nothing if it is not the first day of the month, and likewise cause the second one to return nothing if it is the second day of the month.

And you can then not have to write any IIF() expressions as a bonus.

SELECT 
	TABLE_1.ID_NUMBER ,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) AS [Month] ,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED])) AS [date]
INTO MTD_OR_PMTD
FROM TABLE_1
WHERE 
	(
	(
		((TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE ()) - 1, 1)) 
		AND 
		((TABLE_1.TIME_UPDATED) < DateSerial(Year(DATE ()), Month(DATE ()), 1))
[COLOR="#FF0000"]		AND
		((Day(Now())=1))[/COLOR]
	) 
	)
GROUP BY 
	TABLE_1.ID_NUMBER ,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) ,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED]))

[COLOR="#FF0000"]UNION ALL[/COLOR]

SELECT 
	TABLE_1.ID_NUMBER,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1) AS [Month],
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED])) AS [date]
INTO MTD_OR_PMTD
FROM TABLE_1
WHERE 
	(
	((TABLE_1.TIME_UPDATED) >= DateSerial(Year(DATE () - 1), Month(DATE () - 1), 1))
[COLOR="#FF0000"]	AND
	(Day(Now())>1)[/COLOR]
	)
GROUP BY 
	TABLE_1.ID_NUMBER,
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), 1),
	DateSerial(Year([TABLE_1].[TIME_UPDATED]), Month([TABLE_1].[TIME_UPDATED]), Day([TABLE_1].[TIME_UPDATED]))