Datepart function pulling lastweek of a month

[pctechguy]

I have a table that tracts weekly duties for my department. The weekly duties are dated beginning on monday of each week...I want to &quot;filter&quot; out the last week of a month for each month on the current year. Getting the current year is not a problem year(now()) and I tried doing a datepart(&quot;d&quot;, [date])where results were <=31 and >=25 but February throws me a curve...anything less than 25 gives me 2 weeks in a couple of months.

Wanted results example:

username 1/26/2004
username 2/23/2004
username 3/29/2004
....

 

[BobJacksonNCI]

Hello,

I've marked this thread because I want to see the
solution(s)!

Presuming you want this to continue use in the future,
I think the easiest way would be to link a table
containing the beginning and ending dates. That would
require either a lot of dates or periodic maintenance.
BUT, I can't see a &quot;quick and easy&quot; when you have to
figure out the last Monday's date for each month.

Purests/Gurus here are likely to jump on me for that
one - which is why I'm anxious to see what transpires.

For your consideration, the DateSerial command could
be part of the solution. For more info:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604&Product=acc

HTH,
Bob

 

[Blorf]

try this.

You need the month to force the group by. This will give you the last date for each User name, for each month.

ChaZ

SELECT MyTable.Username, Max([Date]) AS Week, Format([date],&quot;mm-yyyy&quot AS [Month]
FROM MyTable
GROUP BY MyTable.Username, Format([date],&quot;mm-yyyy&quot;


Ascii dumb question, get a dumb Ansi

 

[pctechguy]

Here are the results from the above query...this is close but how can I pull out of this only the records that have a week date that is the last one for the month...for example...01/26/2004 is the last week of January but 04/19/2004 is not...I know this is a tough one and there might not be a solution...thanks

Tony 01/26/2004 01-2004
Tony 02/23/2004 02-2004
Tony 03/22/2004 03-2004
Tony 04/19/2004 04-2004
Tony 05/17/2004 05-2004
Tony 06/14/2004 06-2004
Tony 07/12/2004 07-2004
Tony 08/09/2004 08-2004
Tony 09/06/2004 09-2004
Tony 10/04/2004 10-2004
Tony 11/29/2004 11-2004
Tony 12/27/2004 12-2004
Adam 01/12/2004 01-2004
Adam 02/09/2004 02-2004
Adam 03/08/2004 03-2004
Adam 04/05/2004 04-2004
Adam 05/31/2004 05-2004

 

[Blorf]

What constitutes the last week of the month? The last seven days of any given month, or anything from the last monday of the month to the end of the month?

ChaZ

Ascii dumb question, get a dumb Ansi

 

[MichaelRed]

Public Function basLastWkDay(Optional ByVal dtIn As Date, Optional DOW As Integer = vbMonday) As Date

    'Michael Red    2/26/04
    'To Provide the date of Last Specified Day of the week in a month.

    Dim LastWkDay As Integer
    Dim LastDt As Date

    If (dtIn = 0) Then
        dtIn = Date
    End If

    LastDt = DateSerial(Year(dtIn), Month(dtIn) + 1, 0)
    LastWkDay = Weekday(LastDt)

    Select Case LastWkDay

        Case Is > DOW
            basLastWkDay = DateAdd("d", DOW - LastWkDay, LastDt)

        Case Is = DOW
            basLastWkDay = LastDt

        Case Is < DOW
            basLastWkDay = DateAdd(&quot;d&quot;, DOW - 8, LastDt)
    End Select

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

Good job, Michael!

For those of us less knowledgeable, how could we
pass a date to the function, have it return the
date of the last Monday in the month,

AND, then be able to use that date in selection
criteria of a query?

Without more insight, after getting it to work
I would think there'd be performance hit over
doing it for every record in the table...

Thanks in Advance!
Bob

 

[MichaelRed]

&quot; ... there'd be performance hit over
doing it for every record ... &quot;

Any (ALL) recourse to non-SQL native functions incur a 'performance hit' in database use. Even intrinsic VB/VBA ones.

&quot; ... return the
date of the last Monday in the month ... &quot;

To use in a query, see the ubiquitous {F1} (a.k.a. HELP) with the keyword &quot;Calculated Field&quot;.


&quot; ... be able to use that date in selection
criteria of a query ... &quot;

To actually use it in a query quite depends on some several details, but generally you would just place the criteria in the query (of course it must follow -or perhaps be concurrent to- hte calculated field. Since you appear to not be highly proficient in this, I would suggest starting w/ baby steps and creating the (calcilated) field in hte query grid and placing the &quot;selection&quot; in the criteria row. But then I'm far from sure that I fully understand the issue question.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[pctechguy]

thanks Michael!

This is very close but when I call the function in my where clause on the Date field it only returns the last monday of the current month...I want to move through the table and return the records that match the last monday for each month...not just the current one...is this possible?

Tony

 

[BobJacksonNCI]

pctechguy,

To see this all the way through, how about posting
the SQL of the query you have so far?

Then, we can figure out what has to be done to
get it right. Please copy/paste exactly what you
have so we don't have to wrestle with incorrect
names, reserved words, etc. SQL is a requirement
because the query design grid can't be displayed here.


Michael,

I admire your work, but could be offended if I was
thin-skinned. Yep, I'm not proficient... yet!

If you'd like to jump on an NCR mainframe and
try to calculate the interest due to each of
twelve commercial banks involved in a large participation
loan, each enjoying a slightly different rate but nonetheless required to add up to the amount the customer owes, I expect I'd be more proficient than you...

Since they no longer make NCR mainframes, I had to
find something else to do.

Moderator,

Please be patient with my near-flames. After all,
it's Friday, it snowed/sleeted all day and I have
to drive 40 miles home in it.

Bob

 

[pctechguy]

That is fair and considerate on my part...sorry about that...here you go...

SELECT tblweeklyduties.InfraAdmin, tblweeklyduties.Date
FROM tblweeklyduties
GROUP BY tblweeklyduties.InfraAdmin, tblweeklyduties.Date, Year([date])
HAVING (((tblweeklyduties.Date)=basLastWkDay()) AND ((Year([date]))=Year(Now())));

 

[MichaelRed]

re returning multiple dates:
The function includes two optional arguments. The first is a &quot;Date&quot;, and since I would expect that your records have dates (or date/time) field(s), supplying that field as a parameter would return the Last Weekday specified in the other optional argument, which is set to &quot;Monday&quot; by default (but could easily be set to any day of the week using the enumerated constants &quot;VB&quot; and WeekDayName (e.g. vb Wednesday). Perhaps I should have included a few &quot;sample usage&quot; lines to illustrate this, but I was not really concentrating when Working on this, so 'forgot'.

Here with are two (simplistic) samples for the current year, just so htey are easy to see / check:

For xx = 1 To 12: MyMnth = DateSerial(2004, xx, xx): Print xx, basLastWkDay(MyMnth): Next xx
1 1/26/04
2 2/23/04
3 3/29/04
4 4/26/04
5 5/31/04
6 6/28/04
7 7/26/04
8 8/30/04
9 9/27/04
10 10/25/04
11 11/29/04
12 12/27/04

For xx = 1 To 12: MyMnth = DateSerial(2004, xx, xx): Print xx, basLastWkDay(MyMnth, vbSaturday): Next xx
1 1/31/04
2 2/28/04
3 3/30/04
4 4/29/04
5 5/30/04
6 6/29/04
7 7/31/04
8 8/30/04
9 9/29/04
10 10/30/04
11 11/29/04
12 12/30/04





Re the SQL Statement:

While not necessarily (or even possibly) relevant:

HAVING (((tblweeklyduties.Date)=basLastWkDay()) AND ((Year([date]))=Year(Now())));

is -at best redundant.

It ?shouda, wouda, coudua? be:

HAVING (((tblweeklyduties.Date)=basLastWkDay());

First, the Year function will return the same value wit either argument, since they are just different formats of hte same value, and the difference in the format does not affect the &quot;Year&quot;, so that clause will ALWAYS evaluate to True. Second, the instantation of the baslastWkDay function supplies no argument, so it (also) relies on the &quot;current&quot; date and returns a &quot;Full&quot; date (but without any time / fractional part), so it will restrict the return values to the specific date returned, which WILL be both in the current month and YEAR.

HOWEVER, if someone were to supply a date which were NOT in the current year to basLastWkDay, the date returned would also not be within the current year. While the current configuration (the always &quot;true&quot; clause) changed to some meaningful expression which (perhaps) depended on the year of the date returned from the basLastWkDay function matching hte current year, you could easily get the query to return nothing (the empty recordset.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

Tony,

Please post if/when you have resolved this. I won't be able to look at it again until Tuesday night or Wednesday, due to responsibilities.

I believe sorting and grouping can be handled within the report.

And I think Michael's function will need to be altered, not because it doesn't do what was asked, but because what was asked was incorrect. Given a date, it returns the last Monday of the week, or the last incidence of a specified day of week.

We want to take each date from the table and determine whether that date is between the last Monday of a month and the end of the month. If so, we want the query to select from that row (criteria).

It might be easier to have two functions. In pseudo code, it would be something like:

SELECT ...
FROM ...
WHERE tblweeklyduties.Date BETWEEN basLastMon(tblweeklyduties.Date) AND basEOM(tblweeklyduties.Date)

You'll likely notice, when using this site, that the knowledgeable folks (not including myself in this group, yet) try to provide direction without always spelling out the solution. I.E., often teaching while they assist.
I like it like that!


HTH,
Bob

 

[MichaelRed]

BobJacksonNCI , at the risk of even further generation of offense,

And I think Michael's function will need to be altered, not because it doesn't do what was asked, but because what was asked was incorrect. Given a date, it returns the last Monday of the week, or the last incidence of a specified day of week.


Is not correct. For a given date, it returns the date of the last (specified) week dat of the month. Where the 'specified' week day has a default of &quot;Monday&quot;.

I see no reference to needing the set of dates from the last Monday through the end of the month in any of pctechguy' posts. Please help by citing/quoting the post.


On the other hand, just to be RID of this thread, here is the (single) function which could be used to return any date between the specified week day's date and the end of the Month, albeit with some need for caution / understanding on the part of the user:

Public Function basWkEOM(Optional ByVal dtIn As Date, Optional DOW As Integer = vbMonday) As Date

    'Michael Red    2/26/04
    'Returns the date for days between the _
     Last Week Day specified and the Last Day of the Month. _
     Returns -1 (#12/29/1899#) for dates NOT within the range

    'Sample / Example Usage:
    '? basWkEOM(#2/25/04#)
    '2/25/04

    'Note: This is the "ERROR" Return (or a Date which is NOT in the "range"
    '? basWkEOM(#2/5/2004#)
    '12/29/1899

    Dim LastWkDay As Integer
    Dim LastDt As Date
    Dim LastWkDayDt As Date

    If (dtIn = 0) Then
        dtIn = Date
    End If

    LastDt = DateSerial(Year(dtIn), Month(dtIn) + 1, 0)
    LastWkDay = Weekday(LastDt)

    Select Case LastWkDay

        Case Is > DOW
            LastWkDayDt = DateAdd("d", DOW - LastWkDay, LastDt)

        Case Is = DOW
            LastWkDayDt = LastDt

        Case Is < DOW
            LastWkDayDt = DateAdd(&quot;d&quot;, DOW - 8, LastDt)
    End Select

    If (LastWkDayDt <= dtIn And dtIn <= LastDt) Then
        basWkEOM = dtIn
     Else
        basWkEOM = -1
    End If

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

Tony,

I think you need Michael's first function and:

SELECT tblweeklyduties.InfraAdmin, tblweeklyduties.Date

FROM tblweeklyduties

WHERE tblweeklyduties.Date BETWEEN basLastWkDay(tblweeklyduties.Date) AND DateSerial(Year(tblweeklyduties.Date), Month(tblweeklyduties.Date) + 1, 0);

Recommend you make the query work using only that much before putting GROUP BY or HAVING clauses back in.

Note this is untested.
If Michael is still monitoring, maybe he'll get the drift and point out any corrections needed.

In case the WHERE clause is confusing, it is intended to select rows where the date from the table is between the last Monday of that month's date calculated by Michael's function and the date of the end of that month.

I think it will work, but if it doesn't the likely culprit will be that DateSerial returns a variant instead of a pure date, which may not compare as expected.


HTH,
Bob