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
> Loop For Monthly History, Any Version    
 
   
stephenAA5
post Jul 10 2018, 04:51 PM
Post#1



Posts: 280
Joined: 8-October 09



Good day, everyone! I have been noodling on this to the point that I'm certain I both have killed far too many brain cells, and the ones I have left are not going to solve it for me.

The goal is to take today's date, and find the end of the month for the last 14 months. I'll be using that date for another calculation, with which I shouldn't have any issue. It's the datefinding/loop that I am banging my head on. Any help is greatly appreciated.

I'm sure it's easy, and I'm just missing it.

Thanks!
Stephen.

------------------------------------------------
dim recorddate as date
dim i as integer

i=1
Do While i<15

recorddate= 'last day of the month, i months ago from now.
'Do certain things.

i=i+1
Loop
-------------------------------------------------

Go to the top of the page
 
JHolm
post Jul 10 2018, 05:03 PM
Post#2



Posts: 125
Joined: 7-July 15
From: BC Canada


You could try something like:

CODE
Dim recordate As Date
Dim i As Integer

i = 0

Do While i < 14
     recorddate = DateAdd("m",-i,DateSerial(Year(Date),Month(Date),0))

     'Do stuff

     i = i + 1
Loop


Cheers,

Jeff
Go to the top of the page
 
stephenAA5
post Jul 10 2018, 05:41 PM
Post#3



Posts: 280
Joined: 8-October 09



Thank you, Jeff. I think I need a few weeks of thinking about something else....

Much appreciated.

SC
Go to the top of the page
 
JHolm
post Jul 10 2018, 05:49 PM
Post#4



Posts: 125
Joined: 7-July 15
From: BC Canada


I just got around to testing that and it won't quite work as is. It'll return 05/30/18 for May even though May has 31 days. Try:

CODE
    Dim recorddate As Date
    Dim tempdate As Date
    Dim i As Integer
    
    i = 0
    
    Do While i < 14
        tempdate = DateAdd("m", -i, DateSerial(Year(Date), Month(Date), 0))
        recorddate = DateSerial(Year(tempdate), Month(tempdate) + 1, 0)
        Debug.Print recorddate
        i = i + 1
    Loop


I used another date variable so I didn't have to repeatedly call the DateAdd and DateSerial functions to make it all work on one line. There's probably a more elegant way to accomplish this but it's escaping me right now.

Cheers,

Jeff

Edit: The original code in post #2 would always return a month end day of 30 (28 for February) if run in July. The day portion will be a maximum of whatever is returned for the first month end day. So if you ran it in March it would return 28 for the day portion of every month end.
This post has been edited by JHolm: Jul 10 2018, 05:58 PM
Go to the top of the page
 
stephenAA5
post Jul 17 2018, 10:40 PM
Post#5



Posts: 280
Joined: 8-October 09



Thank you, John. Works perfectly.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2018 - 11:38 PM