I have a IIF statement working but need to set duedate to end of month

[Randy]

I have a query, and an IIF statement. It is simple and work perfectly. I need to adjust it, and cannot seem to think of how.

cashflowagingdetail: IIf([tbldatahistory]![monthyear]-90>[due date],">90",IIf([tbldatahistory]![monthyear]-60>[due date],">60",IIf([tbldatahistory]![monthyear]-30>[due date],">30",IIf([tbldatahistory]![monthyear]-16>[due date],">16 - 30",IIf([tbldatahistory]![monthyear]-0>[due date],">1 - 15",IIf([tbldatahistory]![monthyear]-0<[due date],"Not Due","Current"))))))



so this works just fine as is. But what I need is for the [due date] to be adjusted to month end. So for example something due on 8/12/2014 needs to be "valued" at 8/31/2014. and an item due on 9/5/2014 valued to 9/30/2014, etc.

Any suggestions?

 

[Brianwarnock]

Use DateSerial

Dateserial(year([due date]),month(due date date])+1,0)

will give the end of the current due date month.

BTW it best to avoid spaces in names , not only do you have to keep using [] but in VBA you cannot use intellisense

Brian

 

[Randy]

thank you so much for your help. Yes this application was built over time and takes a feed from our ERP system. The users requested the field names match the ERP system. In hindsight, I should have made the MS Access field names correct, and just gave them "alternate" names for displaying in the Excel pivot table. I know that now.