12 months - How to handle January (1) going back 12 months.

[Rx_]

Used an IIF statement to take care of this. But, it really bothers me.
Can someone suggest a better method? Something more eloquent than an IIF? Using a top 12 in the query with the IIF provides the correct result.

The 2 table fields have Year and Month. Pick a year - Month and select the last 12 months. Using a Month - 1 (e.g. January = 1) creates a problem.

The list box for Months returns a value from 1..12. The year and month were used to generate a 12 month rolling average from Year - Month to Year (Month -1) e.g 2012 June to 2011 July.
When January 2012 hit - the (Month-1) returned a zero. (the application is only three months old so we discovered all January had this problem.

See two attachments. A permit requires samples and later the sample readings populate the record. Once a permit is granted, all of the Samples are created in advance. One reading a month.
The tables were created this way so that auditors could easily understand the process for this fairly new regulation. The auditors in December just loved it. This is mentioned because we know it doesn't meet some of the typical database table design rules.

            Dim lstED_EMMoFlip As Integer ' We subtract one month, will Jan each year -1 is zero - so fix here - HOW CAN THIS BE IMPROVED?
            lstED_EMMoFlip = IIf(Me.lstED_EMMo = 1, 2, Me.lstED_EMMo)
10        On Error Resume Next
20        Me.ID_Well = Me.lst_id_wells
          ' Ed Emission Tanks top 12 query  Use a Combo box  to choose Year and Combo Box to choose Month for sqlBetween
          
30    sqlBetween = "Between #" & Me.lstED_EMMo & "/1/" & Me.lstCalYr & "# AND #" & (lstED_EMMoFlip - 1) & "/1/" & (Me.lstCalYr - 1) & "#"
40    SqlStrTank = "SELECT TOP 12 ED_EMTank.ID_Tank, ED_EMTank.ID_Wells, ED_EMTank.CalYear, ED_EMTank.CalMonth, ED_EMTank.Throughput, ED_EMTank.UnControlled, " & _
      "ED_EMTank.[Actual Controlled],  ED_EMTank.Controlled, ED_EMTank.Efficiency, DateSerial([CalYear],[mo],1) AS YrMoDay "
50    SqlStrTank = SqlStrTank & "FROM ED_EMTank "
60    SqlStrTank = SqlStrTank & "WHERE (((ED_EMTank.ID_Wells)=" & Me.ID_Well & ") AND ((DateSerial([CalYear],[mo],1))" & sqlBetween & ")) "
70    SqlStrTank = SqlStrTank & " ORDER BY DateSerial([CalYear],[mo],1) DESC"
80    Me.lstED_EM_Tank.RowSource = SqlStrTank
90    CreateTempTable SqlStrTank  ' create a temp table of the 12 records That will show up in the Listbox
100   TankTotals                  ' Run a Totals query on the new Temp table - populate the multiple text boxes

Sometimes it is the Little Things that really bother me.

 

[Rx_]

VirtualDate: Format(CDate([CalYear] & "/" & [MO] & "/1"),"mm/dd/yyyy")
Creating a calculated date table on another part of this effort. This uses the table in the attachment above.

 

[Solo712]

Used an IIF statement to take care of this. But, it really bothers me.
Can someone suggest a better method? Something more eloquent than an IIF? Using a top 12 in the query with the IIF provides the correct result.

The 2 table fields have Year and Month. Pick a year - Month and select the last 12 months. Using a Month - 1 (e.g. January = 1) creates a problem.

You could use the DateAdd Access function for the query to avoid the 'IFFying':

sqlBetween = "BETWEEN #" & Format(MyDate,"mm/dd/yyyy") & "# AND #" & Format(DateAdd("m", -11, MyDate), "mm/dd/yyyy") & "#"

Best,
Jiri

 

[Brianwarnock]

I do not fully understand your post as has it stands and from what you say if Feb 2013 is selected you select between 1 feb 2012 and 1 jan 2011 I would have thought that you wanted between 29 feb 2012 and 1 march 2011, but that aside I would use DateSerial to do my calculation

assume ys as year selected and ms as month selected
ys = 2012 ms=2 ie Feb

Datefrom= Dateserial(ys,ms-11,1) gives 1/march/2011
Dateto=dateserial(ys,ms+1,0) gives 29/feb/2012 this technique automatically adjusts for the number of days in the month.

Hope this helps

Brian