UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formula For Number Of Days In The Current Month, Access 2016    
 
   
ssocr
post Mar 31 2020, 07:22 AM
Post#1



Posts: 149
Joined: 10-December 11



I'd like to add a formula to a query which gives me the number of days in the current month. For example if running the query in March the result would be 31. If running the query in April the result would be 30.
Go to the top of the page
 
BentBrain
post Mar 31 2020, 07:33 AM
Post#2



Posts: 563
Joined: 10-February 03
From: Thailand


Hi ssocr
You can use the DateDiff function

DateDiff("d", DateSerial(Year([YourDate]), Month([Your Date]), 1), DateAdd("d", -1, DateSerial(Year(DateAdd("d", 1, [Your Date])), Month(DateAdd("d", 1, [Your Date])), 1)))

Or code example below

mydate = #2/13/03#
numdays = day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))

? numdays
28

Hope this helps
Regards
BentBrain

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
ssocr
post Mar 31 2020, 07:47 AM
Post#3



Posts: 149
Joined: 10-December 11



Thanks BentBrain. I just used the following formula and it returned "30". However, I'd like it to return "31"

NbrDaysInMonth: DateDiff("d",DateSerial(Year(Date()),Month(Date()),1),DateAdd("d",-1,DateSerial(Year(DateAdd("d",1,Date())),Month(DateAdd("d",1,Date())),1)))
Go to the top of the page
 
BentBrain
post Mar 31 2020, 08:00 AM
Post#4



Posts: 563
Joined: 10-February 03
From: Thailand


Use the other formula

Me.txtDate = Day(DateSerial(Year(#3/13/2020#), Month(#3/13/2020#) + 1, 0))

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Larry Larsen
post Mar 31 2020, 08:11 AM
Post#5


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


HI
PMFJI..

Would you be happy in using a UDF (User Defined Funtion), if so try:
Function Days In Month()

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
ADezii
post Mar 31 2020, 08:35 AM
Post#6



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


The trick is to account for Leap Years:
CODE
Public Function fCalcDaysInMonth(dteDate As Date) As Integer
  fCalcDaysInMonth = DateSerial(Year(dteDate), Month(dteDate) + 1, 1) - DateSerial(Year(dteDate), Month(dteDate), 1)
End Function
Go to the top of the page
 
Gustav
post Mar 31 2020, 01:10 PM
Post#7


UtterAccess VIP
Posts: 2,239
Joined: 21-February 07
From: Copenhagen


QUOTE
the number of days in the current month.


That is - for any year/month:

CODE
DaysInMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 0))


or, in a query:

CODE
DaysInMonth: Day(DateSerial(Year(Date()),Month(Date())+1,0))

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
ssocr
post Apr 2 2020, 06:56 AM
Post#8



Posts: 149
Joined: 10-December 11



Thank you everyone. I used Gustav's solution and it works perfectly in my query. (I'm not familiar with where/how to post the formula for a UDF)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 06:37 PM