Dsum with Dynamic Date

[csamy987]

Hi,

I have a source forecast table with the account number and material number as fields, the actual forecast is listed by field names corresponding to their month and year.
ie.
Account #, Material#, 20183, 20184, 20185
3456 1234 3 2 6
7891 2345 2 3 7

I have a working table where I have the account # and material # listed as fields also but I'm trying to pull the addition of the next three months in the forecast.
It looks like this
Account#, Material#, next 3 month forecast
3456 1234 11


7891 2345 12

Right now I have an update query to update the [next3MonthForecast] field by updating to [20183]+[20184]+[20185] which it works but how can I make it dynamic? I don't want to be changing this every month. I have tried [Year(Date())&Month(Date()))] within each bracket (adding 1 for the next month and so on) but it does not work.

I have also tried using a Dsum with the criteria being " [cfield]= 'Year(Date())&Month(Date())' " but again, it does not work. Any help will be appreciated.

Thanks,

[ranman256]

The table would look like: (use real dates)
[date],[acct],[material]
3/1/18, 3456, 1234 11
4/1/18, 3456, 1234 11

then on a form, use a text box to enter the date you want, (txtStartDate)
put another text box,(txtDate3) to calculate the 3 months ahead: = dateadd("m",3,txtStartDate)

then the query will pull 3 months in future.
select * from table where [date] between forms!myForm!txtStartDate and forms!myForm!txtDate3