Find last day of month from a date field

[Maclain]

Morning All,

I'd like to further automate our invoicing system and need a field which has the last day of the month an item was completed.

Currently we have a field in the table called [Date Done]. I'm planning on adding a further field [Tax_Point].

I'd like the field to select the [date done] value and enter the final day of that month, unless, the final date of that month is in the future. in which case it would need the current date.

We create invoices at sporadic times of the month, and in the next month for the previous month; hence the need for a system date check.

 

[pr2-eugin]

Does this Tax Point field really need to be in the table. Go through this link : http://allenbrowne.com/casu-14.html, see if this is essential. If yes. It should not be that hard.

 

[Maclain]

Looking at that, no it doesn't need to be in the table.

Although, we would be using this field for analysis across a range of reports and users. Wouldn't I need to make sure all users knew how to add the field into the query they create for any existing / new reports?

If that's the case, for ease wouldn't it be better to have this within a field in the table rather than troubleshoot the same issue across many new reports? Or am I not looking at this correctly.

 

[pr2-eugin]

If it is depending on some other field mostly (exceptions occur) it doe not need to be stored and needs to be calculated when and where required. You may document the calculation so people can look at how they get the field (if they wish to use) in the Queries/Reports.

 

[CJ_London]

create a function instead that the users can then use in their query.

 

[CazB]

I use

Public Function LastDayOfMonth(ByVal datDate As Date) As Date
    LastDayOfMonth = DateSerial(Year(datDate), Month(datDate) + 1, 0)
    
End Function