Home > Net >  Criteria in MS Access
Criteria in MS Access

Time:09-17

Can anyone help me understand this criteria in MS Access? I just want to know what does 53, ww, and DataPart("ww", [SalesDate]) do in this statement?

Year([SalesDate])* 53   DatePart("ww", [SalesDate]) = Year(Date())* 53   DatePart("ww", Date()) - 1

CodePudding user response:

This is checking to see if SalesDate occurred in the prior week.

The 53 is just converting the year to an equivalent number of weeks (53 because a year is a little more than 52 weeks and you have to account for those extra days). "ww" is an argument to DatePart() that tells it to return the week of the year for the given date (so if it were given 1/1/2021 it'd return "1" for the first week of this year, if it were given 1/11/2021 it'd return "3" because that was Monday of the third week of this year, etc).

So the right side of this expression is calculating the week number for the current date, and subtracting 1 (so last week). And the left side is calculating the week number for SalesDate. If they're equal, SalesDate occurred last week.

CodePudding user response:

This is a convoluted way to check if "Sales Date" was last week. DatePart("ww" [SalesDate]) returns the week number of a the year 1-53. (53 because you can have a partial week at the end of the year, but Microsoft considers this a but see https://docs.microsoft.com/en-us/office/troubleshoot/access/functions-return-wrong-week-number)

By multiplying the year by 53, and then adding the week number, this formula generates a unique week number, and then compares that week number to the week number of the current date minus one, which would be last week.

So this formula simply checks to see if SalesDate was last week.

CodePudding user response:

53 is the count of weeks, i guess... "ww" is the week argument (setting) for the Datepart function..

Datepart function "returns a Variant (Integer) containing the specified part of a given date." So in this case, it's the week.

In function Datepart the SalesDate is your actual date...

CodePudding user response:

This is a convoluted way to check if "Sales Date" was last week.

Indeed. Use DateDiff to reduce this to a minimum:

If DateDiff("ww", [SalesDate], Date()) = 1 Then
    ' SalesDate falls in previous week.
Else
    ' ...
End If
  • Related