CODEWITHSUNDEEP
sqlms-access-2007
dolorithy bustillo
dolorithy bustillo

Reputation: 21

querying for "workweek" in Access SQL

I have a formula from MS Excel 2007 which returns the workweek of a certain date within a certain month.

B1 = IF(AND(WEEKDAY(A2,1)<>1,A2=DATE(YEAR(A2),MONTH(A2),1)),A2-WEEKDAY(A2,1)+7,IF(MONTH(A2)<>MONTH(A2-WEEKDAY(A2,1)+7),EOMONTH(A2,0),A2-WEEKDAY(A2,1)+7))

where A1 = 11/2012 and B1 = 1/7/2012

I'd like to use this formula in MS Access and will return the same value. I'd tried to formulate a query but it won't work.

The field name of column A1 is [rdate] and column B1's field name is [we_date].

Upvotes: 2

Views: 256

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

Don't calculate, query. See:

Why should I consider using an auxiliary calendar table?

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

  • How many business days between x and y?
  • What are all of the dates between the second Tuesday of March and the first Friday in April? 
  • On what date should I expect this shipment to arrive? 
  • What were the dates of all the Fridays in this quarter? 
  • ...  

Upvotes: 4

Related Questions