So a number cannot have a preceding 0. As it renders it useless, unless it is after a decimal point. So, explain in simple terms, what is that you intend to do. So we can offer you the apt solution.
I want to create a 6 month date range eg: 201308 to 201401 (Aug to Jan).
I can easily deduct 5 or 93 to get the correct range that I want but i need to determine wheter the last 2 digits (representing month number) should have 5 deducted or 93.
Because its dynamic.
If the latest month in the table is 201312 I want the latest 6 months (between 201312-5 and 201312). If the latest month in the table 201401 I still want the latest 6 months (between 201401-93 and 201401).
The reason I need to show "If Right(DMax("[MyField]", "[MyTable]"), 2) < 06 Then" is so I can determine which sum is needed.
Why bother with this complex nonsense? Dates are dates, treat them as dates...
Assuming yourfield will hold already the most recent month....
CurrentMonth: dateserial(cdbl(left(yourfield,4)), cdbl(Right(yourfield,2)),1)
6MonthsAgo: dateserial(cdbl(left(yourfield,4)), cdbl(Right(yourfield,2)) - 6,1)
No need for difficult If's or anything, just dynamicaly working with the date (string). If you then want to revert it back to a YYYYMM format, just use the format function around it.
However if one was passing in a number like 201401 would it be treated like a date?
ETA: Yes, it would.
Here is a function based on namliam's idea method of using DateSerial that returns the result as a Long Integer
Code:
Public Function sixMonthsAgo(ByVal smaDate As Long)
Dim varTemp As Variant
' Create a temporary date based on the Year and Month passed in.
' But subtract 6 months from the date
varTemp = DateSerial(Val(Left(smaDate, 4)), Val(Right(smaDate, 2)) - 6, 1)
' Convert the output to a number based on YYYYMM
sixMonthsAgo = Val(Format(varTemp, "YYYYMM"))
End Function
Hi All
I deal with months and years, week numbers etc. So perhaps I can shed a bit of light.
Your 93 or 5 method, i am not sure if this would work in all instances
For having the last 6 months i would use all the fields as dates.
Where does the "201401" data come from?
If I needed to find the last 6 months for whole months I would used VBA to produce something like: #01/dMonth/DYear#
I would format the display for a period as format([dRequiredDate],"mm yy")
Also works for week numbers etc.
If I am grouping by period I would assign another column in my query. (Either dynamic or not) as:fMonth: Format([fDate],'mmyy') Or perhaps Format([fDate], '01/mm/yy')
- but I would leave the actual dates in the query.
I could then group by a period (mm yy) or selected Between [Date1] AND [Date2]
Sorry if the reply is a bit cryptic as I am trying to get it in in my lunch break
Im not a developer and the way I had envisioned wasn’t working which is why I asked for help - Probably could have done without the more discouraging comments.
That's all right, namliam probably thinks I just pinched "his" idea and overcomplicated it (again).
However I am an iterative thinker and go through several versions of code before the final one.
My first attempt, which I deleted as it was slightly more complicated was ...
Code:
Public Function sixMonthsAgo(ByVal theDate As Long)
Dim varTemp As Variant
[COLOR="Red"]' Create a temporary date in MM-DD-YYYY format, based on the Year and Month passed in.
' I added a Day of 14 so Access won't mistake it for a Month
varTemp = CDate(Right(theDate, 2) & "-14-" & Left(theDate, 4))
' Use the DateAdd function to deduct 6 m(onths)
varTemp = DateAdd("m", -6, varTemp)[/COLOR]
' Convert the output to a number based on YYYYMM
sixMonthsAgo = Val(Format(varTemp, "YYYYMM"))
End Function
That used the CDate() function to create a date from a string and the DateAdd() to subtract 6 months.
However, the DateSerial method reduced that to ...
Code:
Public Function sixMonthsAgo(ByVal smaDate As Long)
Dim varTemp As Variant
[COLOR="red"]' Create a temporary date based on the Year and Month passed in.
' But subtract 6 months from the date
varTemp = DateSerial(Val(Left(smaDate, 4)), Val(Right(smaDate, 2)) - 6, 1)[/COLOR]
' Convert the output to a number based on YYYYMM
sixMonthsAgo = Val(Format(varTemp, "YYYYMM"))
End Function
Which was a reduction of code.
I then, using your DMax clause, reduced it again to ...
Thinking that you might want to use something in a query I revisited my original idea, in Post #9, of putting quotes around the "06" to come up with something that could be used within a query, and was not (over)complicated by the use of dates at all ...
I certainly didnt mean to discourage you, instead tried to push ahead to a more generic (in my eyes anyways) solution
I do appologize if I in any way shape or form discouraged you (or worse )
Edit: @ Nanscombe
The (again) part was pointing to this particular problem, not to you in person and certainly not about "pinching" my idea. If I got a euro for everytime that happens I would have been a rich man 10 years ago.
Yes, having possibly answered the posters query by suggesting the placement of quotes around the 06 ("06") in Post 9, I was also trying to come up with a more generic solution.
Not having used DateSerial() before, I was using CDate() and DateAdd() to perform the same task.
Your use of DateSerial() was more more efficient so I adapted my example to use it.
I think this does come back to a reconsideration of the OP's problem though.
if it is HIS database then the dates ought to be dates, not numbers. however, if the dates come from another system, then it may be impractical to change them- in which case, if the dates are numbers in the range
YYYY-MM-DD
(hyphens added for explanation)
eg 2014-05-12 then you have this
if the month is greater than 06, then just deduct 600
so 2014-11-12 becomes 2014-05-12
if the month is less 06 then you need to deduct 9400 (or deduct 10000 ie move to the previous year - and add 600)
(I think that is how the OP got the value 88 (100 - 12))
anyway, 6 months before a given "numeric" date is this
iif(somedate mod 10000 <700, somedate-9400, somedate-600)
I suspect this might be more efficient than all the date manipulation.
I want to create a 6 month date range eg: 201308 to 201401 (Aug to Jan).
I can easily deduct 5 or 93 to get the correct range that I want but i need to determine wheter the last 2 digits (representing month number) should have 5 deducted or 93.
)
