Duration issue in updating a table...NEED HELP! 1

[tadimil]

I have a fertilizer report completed and working which shows a legend of Analysis number and it's color.
It spans a 3 year range and by month. The fertilizer applications are shown under the month and colored according to which fertilizer was used. Currently, it just shows the month of application. I need to show the month of duration. These fertilizers have a "duration" which is in the table. Durations can be 1 month, 3 months, 6 months and so on. Say, January was the application, and the fertilizer's duration is 3 months...I need the fields in the report to be colored for Jan, Feb and Mar.
I've built the table the report runs from via update queries...where there is one record per plant/tree. So a plant/tree can have several applications throughout the year and different types of fertlizers.
The tables has the following fields:
Variety
Lot
Bin
Trees
Color
Analysis
Year1
Year2
Year3...all the way to Year36

The Year1 thru 36 contain the color number thats associated with the analysis. So...currently its like this:

Year1 Year2 Year3...etc
255

I need it to be...if say its for 3 month duration to update to:

Year1 Year2 Year3...etc
255 255 255

Year1 is January of previous year
Year2 is February of previous year
thru Year12
Year13 is January of current year
thru Year24 as December of current year
Year25 is January of next year
thru Year36 which is December of next year.

I've tried to be as detailed as possible...if you need anymore info...I'll try to provide ASAP I'll be glad to send snap shots of the report and modules if you need them.
Thanks in advance!

 

[lameid]

I'm not sure how you are getting Year1, Year2 etc. but I would assume it is some sort of crosstab query?

At anyrate, I would create a table with each month as a date. For simplicity use the first day of the month.

1/1/2006
2/1/2006
3/1/2006
etc..


Then you could add this table to a query with your other query. DO NOT JOIN THEM. Instead use values from your existing table to get values from this newly created table.

Say the new table is Calendar with a field of DateId.

Where Calendar.Dateid between StartDateFromTable and Dateadd(DurationQtyFromTable,DurationTypeFromTable, StartDateFromTable)

I hope this all makes sense... I didn't get enough from your original post to be more specific.

 

[tadimil]

I'll try to be a little more clear...sorry...it's kinda confusing.
Below is the report:

Here is the table/ReportOne that is updated using 36 update queries that are set in a module/called on report Open, using table/ReportTable below it:

Basically...using ReportTable to create the ReportOne table to make one record for each plant/tree.

Below is the report code, it is to pull in the colors off of the ReportOne table. This is in the Reports Detail Onformat:

I need to get the ReportOne table to reflect the "Fertilizer Duration", which could be 1, 2, 3, 6, 12...etc..from the ReportTable. The ReportOne table needs to have consecutive colors according to the duration.
So, above lets use the Variety: NAC-007, its the second record of the ReportOne table. Notice it has been fertilized on 7/2005 and 11/2005. Look at the ReportTable, it shows a duration of 3 for each of these applications.
Go back to the ReportOne table and this is where I need to continue updating the fields Year8 and Year9 and then Year12 and Year13. So the color will pull up on the report. I need to get the color number in those fields on the ReportOne table, using the duration number from the ReportTable.
Final result, the report will have J/2005,A/2005,S/2005 Fields colored and N/2005,D/2005,J/2006 colored.

Hope this a bit clearer. Its pretty confusing. Thanks again in advance and for any assistance.

 

[lameid]

So what does the query look like to get from ReportOne to the ReportTable? That is where my idea will come in... also if you could give me a select statement listing all the fields from report one, that will proably be helpful in creating a new one.

 

[tadimil]

get from ReportOne to the ReportTable"
It should be from ReportTable to ReportOne. Here is the query. First the ReportOne Table is emptied and the Append/Insert query loads the destinct records from ReportTable to the ReportOne. Then the Update queries bring in the colors according to month and year for each plant/tree.

RunQuery ("DELETE * FROM ReportOne;")
RunQuery ("INSERT INTO ReportOne ( Variety, Lot, Bin, Trees )SELECT DISTINCT ReportTable.Variety, Trim([Lot Number]) AS Expr1, Trim([Bin]) AS Expr2, ReportTable.[Number of Trees]FROM ReportTable;")
'Update Previous Year
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year1 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""1/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year2 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""2/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year3 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""3/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year4 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""4/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year5 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""5/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year6 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""6/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year7 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""7/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year8 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""8/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year9 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""9/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year10 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""10/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year11 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""11/"" & previousyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year12 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""12/"" & previousyear()));")
'Update Current Year
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year13 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""1/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year14 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""2/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year15 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""3/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year16 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""4/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year17 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""5/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year18 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""6/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year19 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""7/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year20 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""8/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year21 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""9/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year22 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""10/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year23 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""11/"" & currentyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year24 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""12/"" & currentyear()));")
'Update Next Year
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year25 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""1/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year26 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""2/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year27 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""3/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year28 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""4/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year29 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""5/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year30 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""6/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year31 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""7/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year32 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""8/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year33 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""9/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year34 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""10/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year35 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""11/"" & nextyear()));")
RunQuery ("UPDATE ReportOne INNER JOIN ReportTable ON (ReportOne.Trees = ReportTable.[Number of Trees]) AND (ReportOne.Bin = ReportTable.Bin) AND (ReportOne.Lot = ReportTable.[Lot Number]) AND (ReportOne.Variety = ReportTable.Variety) SET ReportOne.Year36 = [reporttable].[report color] WHERE (((ReportTable.DateFinished)=""12/"" & nextyear()));")

End Function

Here is the select statement to show the fields in ReportOne:

SELECT Variety, Lot, Bin, Trees, Year1, Year2, Year3, Year4, Year5, Year6, Year7, Year8, Year9, Year10, Year11, Year12, Year13, Year14, Year15, Year16, Year17, Year18, Year19, Year20, Year21, Year22, Year23, Year24, Year25, Year26, Year27, Year28, Year29, Year30, Year31, Year32, Year33, Year34, Year35, Year36
FROM ReportOne;

Thanks again! You don't know how much I appreciate your help...

 

[tadimil]

Anyone have any ideas for this?

 

[lameid]

Hmmm... Sorry I didn't get back sooner. The short version is I really need a new job.

Anyways, you are taking a different approach to reporting than I would. I would make a crosstab query that shows the months accross the top as row headings and use year as a column heading. I realize this is different. To ensure you have all the months you could use the calendar table as I mentioned before and get a range on it's values.

You can even set the Column Headings property of the month field to something like "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

See my original post for date ranges.

Using my idea you have a few options. First change your report to show one year at a time (I doubt this is o.k. but it would be the easiest to write and fastest to run). Second you could make three crosstab queries, one for each year and outer join to them on Lot's and Bins.

Maybe the easier thing for you to do would be to write a maketable query based on my calendar table idea and then update off of it instead of your existing table. That probably would be the easiest way to fix the problem. Although be wary of temp tables. Definitely do not put them in the backend. And make sure you have a front end backed up.

 

[tadimil]

I appreciate you getting back to me. I do need it to show the 3 year range on one report. But, I apologize, I am just not understanding as to how this is going to update consecutive months if the application is a 3 month duration...how do you get the next two consecutive months updated to the same record as the first months color? Crosstabs only pull in info if it is there...I need everything there whether or not it received an application or not.
Maybe I'm making this way harder than necessary...I tend to do that...sorry to be so dense here...if you had an example? or I was thinking I'd have to do something like a loop to look at the duration from one table and repeat the update that many times for the other table on the consecutive months...? I just don't know the coding for that...don't worry to much about it....I do appreciate your time! Thanks! I'm like you...I need a new job too! LOL

 

[lameid]

I wrote most of this and then realized you are not storing a date for date finished but most likely text. I would recommend changing it as I had assumed you were just storing the month with the first day of the month and year in a datetime field when thinking about it (MM/1/YYYY)... I've got a workaround following the below though.

Create a table, Calendar with a field DateID and a date/time datatype.

Next populate it with the first day of each month.

1/1/2005
2/1/2005
3/1/2005
...
11/1/2007
12/1/2007


In the below SQL I'm guessing that [Fertilizer Duration] is the correct field name as it is truncated in your screen print...

Select Calendar.Dateid as MonthToMark, ReportTable.*
From Calendar, ReportTable
Where Calendar.DateId Between ReportTable.DateFinished and dateadd("m",ReportTable.[Fertilizer Duration],ReportTable.DateFinished)

That should give you your ReportTable contents and a field MonthToMark as the date I understand you want to Color on your report.

The catch with this is to ensure that calendar is populated with all the months.


The workaround... Assuming the format for DateFinsihed is consistent, you could add a field with those contents to the calendar table. Then just add another copy of it to the query above and join it to the DateFinished field. Alias the new copy of the calendar table to something like CalendarFinished. Your where clause would then be...

Where Calendar.DateId Between CalendarFinished.DateID and dateadd("m",ReportTable.[Fertilizer Duration],ReportTable.DateFinished)

Sorry, but I didn't want to think about fixing the From clause with the join etc.

I hope this all makes sense.

 

[tadimil]

Sorry..I tried to do as you suggested but it's not working. The ReportTable ends up with 2 fields with the same content just two different names, "MonthToMark" and "DateFinished". The ReportOne table is my final table and its not mentioned in your suggestion, it contains the fields for the colors. Is there a way to do this with recordsets and arrays maybe?
Thanks again!

 

[lameid]

If you did as I suggested you have all the fields for ReportTable plus a MonthToMark Field.

The part I left out is you use this query to populate your final table using MonthToMark instead of Date Finished in your queries. For a record in your ReportTable you should have as many records as months you want to mark. One record for each month to mark. Does that help?

 

[tadimil]

Yes...I understand it better...I will try it and let you know how it turns out...thank you!

 

[MajP]

You asked for a look. May approach is to edit the Report One table. I think this may do it

Public Sub fillDuration()
  Dim rsReportOne As DAO.Recordset
  Dim intDuration As Integer
  Dim strVariety As String
  Dim intMonthCounter As Integer
  Dim intDurationCounter As Integer
  Dim strFertilizedMonthField As String
  Dim strNextMonthField As String
  Set rsReportOne = CurrentDb.OpenRecordset("ReportOne", dbOpenDynaset)
  'Loop through all records
  Do While Not rsReportOne.EOF
    strVariety = rsReportOne.Fields("Variety")
    'find the fertilizer duration for a record in the Report One table
    intDuration = DLookup("Fertilizer Duration", "ReportTable", "Variety = '" & strVariety & "'")
    'Loop through all the YearXX fields
    For intMonthCounter = 1 To 36
      'If the field has a value in it then set that as the Fertilized field
      If rsReportOne.Fields("Year" & intMonthCounter).Value <> 0 Then
          strFertilizedMonthField = "Year" & intMonthCounter
          For intDurationCounter = 1 To (intDuration - 1)
            'Do not add values past Year36
            If intMonthCounter + intDurationCounter = 36 Then
              Exit For
            End If
            strNextMonthField = "Year" & intMonthCounter + intDurationCounter
            'Check to see if the next month has a zero in it
            'If it does not there has been an early fertilization before the end of the
            'duration
            If rsReportOne.Fields("strNextMonthField") = 0 Then
              rsReportOne.Edit
              rsReportOne.Fields("strNextMonthField") = rsReportOne.Fields("strFertilizedMonthField")
              rsReportOne.Update
            Else
              Exit For
            End If
           Next intDurationCounter
       End If
    Next intMonthCounter
  Loop
 End Sub

Here again, there are a lot of places for me to have made logic errors or bugs. So I do not guarantee anything. The idea is the same as the last post.
I read through the Year fields until I come to one with a value in it. I then get the duration. I take the value from the fertilezed month, and stick it in the next fields.
There is a logic trick in here with the check to see if the "strNextMonthField" has a zero or a value.

 

[tadimil]

Okay...you did it! Thank you a thousand times over!

 

[tadimil]

My last post above was for lameid...it worked.

MajP, I will also apply your suggestion...and will post the outcome as soon as I try it. I appreciate your taking time to look at this!

 

[lameid]

It took me a little while to digest but I think MajP's suggestion will work, after fixing a few syntax errors.

The entire if statement begining with

If rsReportOne.Fields("strNextMonthField") = 0 Then

References the Fields collection with the variable name in double quotes... take out the double quotes.

rsReportOne.Fields("strNextMonthField")

Should be

rsReportOne.Fields(strNextMonthField)

Beyond that, his code fixes the ReportOne Table you had already created. It is an additional step either way. I think my solution will run faster. However, you do have to keep that calendar table populated so that alone may give you cause to go with the other method.

Besides the error, you may also want to open a recordset and do a find instead of using Dlookup. Dlookup is inherently slow. Recordsets are definitely faster if you you leave them open.

As a general note (since performance came up), queries are faster than recordsets so it is faster to open a query with just the records that need to be processed than it is to open an entire table and just repeatedly find the records you are after. The query speed advantage is why I expect my method to be faster... Especially if you index DateEffective.

 

[MajP]

I have to agree, it is almost always faster to do it in sql then looping through a recordset. I bet I could write code to replace your update queries that would be a tiny fraction of the size of all your code, but it likely would not be any faster. I imagine my code is somewhat slow. However, I would suspect that your formatting overshadows any gains that could be made in using queries. "iif" are slow, and you have 72 of them per record. Unfortunately, I could not come up with a better way.