- Local time
- Today, 13:19
- Joined
- Feb 28, 2001
- Messages
- 28,310
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.
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.