Dates - Get the last two months

[Leo]

Hello all. I need to build a query based on user input. What the user inputs
is the number of months and the program will do some calculations based on
the dates

so if a user enters 2, this means two months. Which is ok. But here's the
tricky part: 2 means the most recent months that have data.

so if I am in August running the program and enter 2, then I want that
program to select the most 2 recent months for which there is data. Ideally
it should be: June and July in this case. But that's easy.compared to when
there's data for July but there's no data for June. In this case I have to
go back until I find the next month with data and select everything for
those two month as well to make the two months

So I'm going back 2 months, starting with the most recent that has data,
going back until I find the next one and stopping there


So my question is, has anyone ever done this? If yes/or not could you give
me some ideas please? I'd greatly appreciate it

Leo

 

[Guest]

That would be the basic premise, but it may get a little confusing keeping
track of it. So what if you go back two months and there is no data for
either? I don't know how you are doing it, query, or however, but here is
some Psuedo code logic that may be an approach to try:

GotMonths = 0
Do While GotMonths < 2
Check previous month
If got some data then
Do your stuff
GotMonths = GotMonths + 1
End If
Loop

 

[Leo]

Actually it goes back to infinity...so there's no starting date.
So in pseudo code it would be something like

Months=0

GetCurrentDate

Do While Months > 2

StartGoingBack

MonthHasData= True
StoreMonth
Months = 1
Loop

 

[Guest]

That will never execute. You set months to 0 then test to see if it is > 2.
It will not even go through the first iteration. If you look at my code,
GotMonths starts at 0.
Check Previous Month means in August, Check July.

If July has data, then you do what you need with the July data and add 1 to
GotMonths (Now GotMonths = 1 - means we have 1 month's data).
Now test for June. etc, etc, etc.
For each month you get data, GotMonths will increment by 1. When it gets
to 2, we have our two month's data, and we are done.