How can I find the week ending date?

[BarbQb]

Hi All,

Edit:

I currently have table where I need to find the Week Ending date for a rolling 15 weeks, where each week is a separate column. It goes from Sched01 - Sched15.

The Sched01 column is always the current Week Ending Date (Saturday). However, if a month ends on any other day but Saturday then I need the month end date to be the Week Ending Date.

For example as of 7/22/2011:
Sched01 - Week Ending Date is currently 7/23/2011
Sched02 - Week Ending Date is currently 7/30/2011
Sched03 - Week Ending Date is currently 7/31/2011
Sched04 - Week Ending Date is currently 8/06/2011

For example as of 7/25/2011:
Sched01 - Week Ending Date is currently 7/30/2011
Sched02 - Week Ending Date is currently 7/31/2011
Sched03 - Week Ending Date is currently 8/06/2011
Sched04 - Week Ending Date is currently 8/13/2011

Any help is appreciated.

[Rabbit]

If you subtract the weekday number of the date from 7, and then add that many days to the date, that will give you the week ending date.

Then you check if that date is in the next month, if it is, you use the end of the month.

[NeoPa]

What's in your table?

Always 15 records? A history including 15 for the upcoming weeks?

When are you requiring to show/use these values?

There are so many possible approaches to this, that without more info it's unprofitable trying to answer.

[BarbQb]

NeoPa-

The table contains product production by week.

One record is product X and the Production Amount in the following 42 weeks (columns Sched01-Sched42), but I am only interested in the first 15 weeks (columns Sched01-Sched15).

There is no history in the table. Sched01 is always the current Week Ending Date.

I would like to show everything that is being produced in each of the 15 weeks, but the problem is finding the dates for each week. The users will be checking this schedule 2-3 times per week.

Eventually I would like to join it with another table that shows the product that is coming in each week. I have already configured the dates for that table, but they are not set up the same way.

Please let me know if you need any more information.

[NeoPa]

So I guess you have a form or report, or other similar object, where you wish to update the display for the column titles so that instead of showing just Sched01, Sched02, etc, it shows the W/E dates for each column. Is that right?

If so, what type of object are we dealing with? A form? A report?

[BarbQb]

Sorry about that.

I would like for the column titles to be the W/E date and not Sched01, 02,... I am just working with a query right now. Eventually I will put it into a report.

[NeoPa]

Right. I think we need to get something clear here at this point :

Column titles in queries cannot be determined from within the query. Column titles can be assigned to a query, but not from within the query itself. The columns must be available and in the query (design) itself and are not dependent on, or ralative to, the data at any particular time of processing the query. Furthermore, even if it were possible to determine the column headings in this way for the query, it would make it impossible/insanely difficult to use that query for a report. It is possible to create the query (SQL) with your desired columns titles in VBA code on the fly. Because this would be unproductive though, as far as using in a report, I will ignore this for now.

All that said, you can only really benefit by determining the titles within the report itself (This would also be true for a Form if that were your requirement).

Before continuing I'd like you to confirm that you understand and accept what I've just said, and my proceeding along these lines will make sense. It does mean, of course, that your development of the query can go ahead without further delay as there is really no need at this point to worry about the titles (The controls in the eventual report will need to be bound to fields from the query with names that are known in advance. IE. The known ones of Sched01, Sched02, etc.).

[BarbQb]

Thanks NeoPa. I think I jumped ahead of myself when I said I wanted to change the column titles in the Query. I definitely had an idea in my head that will not be plausible.

But, I do understand and agree that it will be beneficial to figure out the Week Ending Dates within the report.

[NeoPa]

I can't test this without your database but it should work hopefully (It compiles ok at least) :

Option Compare Database
Option Explicit
 
Private Sub Report_Open(Cancel As Integer)
    Dim intSched As Integer, intMonth As Integer
    Dim datNext As Date, datSat As Date
 
    datNext = Date
    datSat = DateAdd("d", 7 - Weekday(datNext), datNext)
    For intSched = 1 To 15
        If Month(DateAdd("d", 1, datNext)) = Month(datSat) Then
            datNext = datSat
            datSat = DateAdd("d", 7, datSat)
        Else
            datNext = DateAdd("d", -1, CDate(Format(datSat, "m/yyyy")))
        End If
        With Me.Controls("txtSched" & Format(intSched, "00"))
            .Caption = Format(datNext, "ddd d mmm yyyy")
        End With
    Next intSched
End Sub

You can choose how you want the column titled by changing line #18.

[BarbQb]

Thank you very much, NeoPa. I will let you know how it works.