I have a table that includes a client account number, revenue, and a Date in the format YYYYMM. I would like to create a query that sums the revenue for a client where the date is six months prior to today.

Thanks in advance

You should create a field that formats the date to MMYYYY format, or not sure this will work.

Criteria for the date field should be:

Bteween DateAdd("m", -6, [datefield]) And Date()

woknick,

If your date field [StringDate] is really "formatted" like "YYYYMM" then it is a string.

Make a new Column in your query:

RealDate: cdate(Mid([StringDate], 5,2) & "-" & Mid([StringDate], 7,2) & "-" & Mid([StringDate], 1, 4))

It doesn't have to be a visible.

For Criteria:

Between DateAdd( "m", -6, Date()) And Date()


If [StringDate] is really a Date field then just specify the criteria.

Wayne