Getting the last day of the month

[Jeremy Ames]

I want to build a query that shows the last day of the month for a given
date. I know how to use DateAdd and DatePart functions to create this is
module lebel code, but I want this information in a query. This is the code
that I am currently using in a module:

dim lDate as long, nMonth as int, sReturnDate as string

lDate = DateAdd("m", 1, SalesDate)
nMonth = DatePart("m", lDate)
sReturnDate = DateAdd("d", -1, nMonth & "/1/" & datepart("y", SalesDate))

That returns the correct date, but I cannot figure out how to do it in a
query. Any help would be greatly appreciated.

Thanks in advance,

Jeremy Ames

 

[fredg]

I want to build a query that shows the last day of the month for a given
date. I know how to use DateAdd and DatePart functions to create this is
module lebel code, but I want this information in a query. This is the code
that I am currently using in a module:

dim lDate as long, nMonth as int, sReturnDate as string

lDate = DateAdd("m", 1, SalesDate)
nMonth = DatePart("m", lDate)
sReturnDate = DateAdd("d", -1, nMonth & "/1/" & datepart("y", SalesDate))

That returns the correct date, but I cannot figure out how to do it in a
query. Any help would be greatly appreciated.

Thanks in advance,

Jeremy Ames

Day 0 (Zero) of a month is the last day of the previous month.

LastDayOf PreviousMonth:
DateSerial(Year([SalesDate]),Month([SalesDate]),0)

 

[Jeremy Ames]

A little deviation of that worked great. The function that you had listed
pulled the last day of the previous month, where as I needed the last day of
the current month, but I figured it out from there. Thanks for your
assistance.

I want to build a query that shows the last day of the month for a given
date. I know how to use DateAdd and DatePart functions to create this is
module lebel code, but I want this information in a query. This is the code
that I am currently using in a module:

dim lDate as long, nMonth as int, sReturnDate as string

lDate = DateAdd("m", 1, SalesDate)
nMonth = DatePart("m", lDate)
sReturnDate = DateAdd("d", -1, nMonth & "/1/" & datepart("y", SalesDate))

That returns the correct date, but I cannot figure out how to do it in a
query. Any help would be greatly appreciated.

Thanks in advance,

Jeremy Ames

Day 0 (Zero) of a month is the last day of the previous month.

LastDayOf PreviousMonth:
DateSerial(Year([SalesDate]),Month([SalesDate]),0)