Rolling dates from text field

  • Thread starter Thread starter Jani
  • Start date Start date
J

Jani

I have a 'date' field that is text (because of mainframe), i.e. 200909,
200908, etc. and an amount column. What is the calculation that I should be
putting in the criteria of a query to pull the previous 6 months of data. I
prefer not to use fields on a form and make this more automatic. For
instance, today is November 1, I would want to pull 200910 - 200905. Do I
need to reformat this into a date?Thanking you in advance!
 
I have a 'date' field that is text (because of mainframe), i.e. 200909,
200908, etc. and an amount column. What is the calculation that I should
be putting in the criteria of a query to pull the previous 6 months of
data. I prefer not to use fields on a form and make this more automatic.
For instance, today is November 1, I would want to pull 200910 - 200905.
Do I need to reformat this into a date?Thanking you in advance!
Click to expand...


BETWEEN Format(DateSerial(Year(Date()), Month(Date())-7, 1) ,"yyyymm")
AND Format(DateSerial(Year(Date()), Month(Date()), 0),"yyyymm")
 
Hi Jani

Just to clarify, since you say, "today is November 1, I would want to pull
200910", does this mean that you ALWAYS want to run the report for the six
month up to the end of last month?

That aside, you need to use the FORMAT and DATEADD functions.

FORMAT function example: FORMAT(Date(),"YYYYMM") - formats today's date as
200911

DATEADD function example: DATEADD("m",-6, Date()) - takes 6 months away from
today's date.

You can use a combination of these two to create your Where clause.
Something like:

WHERE (MyTable.MyDateField BETWEEN (Format(DateAdd("m",-7,Date()),"YYYYMM"))
and (Format(DateAdd("m",-1,Date()),"YYYYMM")))
 
Back
Top