473,381 Members | 1,419 Online

# Showing the last day in month Function

Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol
Nov 12 '05 #1
3 63074
"Gol Yass" <go********@yahoo.com> wrote in message
news:bj*********@news.emirates.net.ae...
Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol

You will have to write your own function, but if you remember that the last
day of any month occurs 1 day before the first of the following month, it's
easy.

Eg This function takes any date and returns the last day of the month of
that date:

Function LastDay(dteAny As Date) As Date
LastDay = DateSerial(Year(dteAny), Month(dteAny) + 1, 1) - 1
End Function
HTH

Fletcher
Nov 12 '05 #2
Gol Yass wrote:
Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol

I use this function, which just returns the number (i.e. 29, 31, 30):

Month(DateVar) + 1, 1)))

Of course, it's probably the "long way"...

--
Jon Trelfa
There 10 kinds of people in this world...
Those who understand binary and those who don't

Nov 12 '05 #3
"Merle Nicholson" <me******@tampabay.rr.com> wrote in message
news:QK******************@twister.tampabay.rr.com. ..

Here's what I use.
Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
' ************************************************** **********
' Given any valid date, returns the last day of that month
' *****************************
LastDayInMonth = DateAdd("m", 1, CDate(Month(AnyDate) & "/01/" &
Year(AnyDate))) - 1
End Function

Merle: I'd strongly suggest changing your function to:
Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
' ************************************************** **********
' Given any valid date, returns the last day of that month
' *****************************
Month(AnyDate), 1) - 1
End Function

The CDate function respects the user's date format settings. Anyone using
your function who has their Short Date set to dd/mm/yyyy will get incorrect
results.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 0 by: Sebastian | last post by: I have a query that runs perfectly when doing a date equal comparison (see last line in the subquery: AND T2.DQ_DAT = '14.02.2005'). A direct comparison of the date is not sufficient for my... 0 by: Kate | last post by: Hi... I have a foxpro program which is for producing excel reports back ended to foxpro table(.dbf). Since the table is getting bigger and bigger, it slows down my program. I need a program... 2 by: Daniel Tan | last post by: In my code, i tried to use month function but it returned an type mismatch error. Pls help. Thanks. Dim mymonth As Integer mymonth = Month() * is a date type Rgds, Daniel 7 by: MLH | last post by: Public Function GetLastDayOfMonth(ByVal dtDay As Date) As Date '************************************************************************** ' Accepts a date. Determines month & year of the date.... 2 by: Vijay Balki | last post by: I need to view a discussion posted last month in this group. How do I see it? VJ 5 by: jenkinsloveschicken | last post by: Could someone please take a look at my query and tell me what I am missing? The strange thing is that when I first built the query, it was working fine. Then it broke. I suspect the problem is the... 3 by: paulvonhippel | last post by: I'd like to count the number of transactions for each debit card and month over a 3-month period. But this query -- select DBT_CRD_DIM_NB, month (tran_dt) as tran_mnth, count(*) as... 1 by: Mufasa | last post by: So I have a calendar control - and I have it automatically select last week. So this week it selected 8/24/2008 and 8/30/2008 which is what I want. Problem is - it's showing September. How can I... 3 by: Coolboy55 | last post by: Hi everyone, I'm having some trouble with a query. I have a history table where one of the fields is a date stamp. My query takes a date from the user, and I want the query to return all records... 0 by: isladogs | last post by: The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former... 0 by: taylorcarr | last post by: A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,... 0 by: Charles Arthur | last post by: How do i turn on java script on a villaon, callus and itel keypad mobile phone 0 by: aa123db | last post by: Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function \$name\$ (\$parameters\$) { } ... 0 by: ryjfgjl | last post by: If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming... 0 by: ryjfgjl | last post by: In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files... 0 by: BarryA | last post by: What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress... 1 by: nemocccc | last post by: hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions? 0 by: Hystou | last post by: There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...