Keep Preceding 0 in VBA (1 Viewer)

[The_Doc_Man]

I might take one of two approaches.

If this yyyymm date field originates within my own database, I would convert it to a DATE field and do date comparisons.

However, if the yyyymm date field originates externally, it can be brought in as a number OR as a string. I would convert it to a 6-character string. Then split the yyyy and mm parts, form them into a string that looks like 01/mm/yyyy, and do a CDate on it to convert it locally to a date field that can be manipulated.

In either case, I have a date field that can become the argument of a DateAdd function that adds a negative 6 months to the date. That gives the 6-month window.

Here is the mind-set you need for this kind of problem: Ask your self first, where do I need to be; next, from where do I come? Last, how do I get there from here.

I HAVE - yyyymm date field either numeric or text
I WANT - something that allows me to manipulate dates (which includes the DateAdd function)
To get there - I must convert the yyyymm date to a full (normal) date.

That sounds a bit pedantic, but the truth is that Access gives you SO many options to get from point A to point B that sometimes, particularly for newer developers, it can be overwhelming.

 

[nanscombe]

...
However, if the yyyymm date field originates externally, it can be brought in as a number OR as a string. I would convert it to a 6-character string. Then split the yyyy and mm parts, form them into a string that looks like 01/mm/yyyy, and do a CDate on it to convert it locally to a date field that can be manipulated.

In either case, I have a date field that can become the argument of a DateAdd function that adds a negative 6 months to the date. That gives the 6-month window.
...

Indeed, That's what I was achieving in the piece bit of code in Post 16.

Public Function sixMonthsAgo(ByVal theDate As Long)
Dim varTemp As Variant

  ' 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 = [COLOR="Red"]CDate(Right(theDate, 2) & "-14-" & Left(theDate, 4))[/COLOR]
  
  ' Use the DateAdd function to deduct 6 m(onths)
  varTemp = [COLOR="red"]DateAdd("m", -6, varTemp)[/COLOR]
  
  ' Convert the output to a number based on YYYYMM
  sixMonthsAgo = Val(Format(varTemp, "YYYYMM"))

End Function