How do I calculate if a date range falls within a specific month

[lmn]

Hello,

I want to mark an X under the month if a project occurs during that given month.

For example - say I have 3 vendors and 8 projects

Start Dt End Dt
Vendor A 01/01/03 05/04/03
Vendor A 02/03/03 02/04/03
Vendor A 04/01/03 12/01/03
Vendor B 01/05/03 06/02/03
Vendor B 02/03/03 02/28/03
Vendor C 06/01/03 07/01/03
Vendor C 08/01/03 12/31/03
Vendor C 02/06/03 12/31/03

I want to show all of the months across the top - and then each vendor (like a cross tab). If the vendor has a project that falls within that month - it marks an X - if not, it leaves it null.

I'm going nuts trying to figure this out. In my head I keep going over different possibilities of how to say - if it's in this timeframe - mark it - but I can't succeed

Any suggestions? Someone else must have done this before.

On another note - is there a way in Access to specify which week of the year (1-52) something is? I thought I could do this in SQL (I haven't coded in SQL in 3+ years) - but I don't recall and an Internet search hasn't provided me any results for Access.

Thanks so much,
Lisa

 

[jdgreen]

How is the report set up? The best way I can think of is to use a If/Then statement. Specify the start date by saying it is less than the end of the month and the end date is greater than the end of the month. That should do what you want. The only problem would be you would have to update the query each year.

John

 

[ReluctantDataGuy]

Well, all hail reluctantdataguy! Actually, I'm sure there's a better way. I'm no SQL wizard. But I got it in two queries.

First, you're missing a little structure to the data. It should be similar to this (I added the Project #):

Vendor Project Start End
Vendor A Project 1 1/1/2003 5/4/2003
Vendor A Project 2 2/3/2003 2/4/2003
Vendor A Project 3 4/1/2003 12/1/2003
Vendor B Project 4 1/5/2003 6/2/2003
Vendor B Project 5 2/3/2003 2/28/2003
Vendor C Project 6 6/1/2003 7/1/2003
Vendor C Project 7 8/1/2003 12/31/2003
Vendor C Project 8 2/6/2003 12/31/2003

Next, to get the month range, I created "Query1" as follows (copy and paste into SQL view for a new query):

SELECT [tbl WEEKS].MonthStr AS [Yr/Mo], tblVendors.Vendor, tblVendors.Project, tblVendors.Start, tblVendors.End
FROM tblVendors, [tbl WEEKS]
GROUP BY [tbl WEEKS].MonthStr, tblVendors.Vendor, tblVendors.Project, tblVendors.Start, tblVendors.End
HAVING ((([tbl WEEKS].MonthStr) Between Format(DMin("[Start]","tblVendors&quot,"yy/mm&quot And Format(DMax("[End]","tblVendors&quot,"yy/mm&quot));

Then I created the following crosstab query (again, copy this and paste it into SQL view for a new query):

TRANSFORM First(IIf([yr/Mo] Between Format([Start],"yy/mm&quot And Format([End],"yy/mm&quot,"X",Null)) AS Expr1
SELECT Query1.Vendor, Query1.Project
FROM Query1
GROUP BY Query1.Vendor, Query1.Project
PIVOT Query1.[Yr/Mo];

I have to admit I was suprised to see the following result:

Vendor Project 03/01 03/02 03/03 03/04 03/05 03/06 03/07 03/08 03/09 03/10 03/11 03/12
Vendor A Project 1 X X X X X
Vendor A Project 2 X
Vendor A Project 3 X X X X X X X X X
Vendor B Project 4 X X X X X X
Vendor B Project 5 X
Vendor C Project 6 X X
Vendor C Project 7 X X X X X
Vendor C Project 8 X X X X X X X X X X X


Okay, well, it doesn't look so good here, but in Access its BEAUTIFUL!!!

 

[ReluctantDataGuy]

Whoops! Forgot to mention. For the example, I used a table name of "tblVendors". Replace your table name and field names throughout and it should work...

 

[lmn]

Thanks for your response....but what is TBL WEEKS? Do I need to create a table for all of the weeks in a year???

L

 

[ReluctantDataGuy]

Oh... you're right! I forgot to mention that. Yes, well, I call it [tbl WEEKS] cause I use it for a lot of charts that use weeks. Its a table filled with dates and other things related to each date that come in handy now and then. In fact, I just added the MonthStr field for your situation.

Before I get into details on how to create this table, one word of advice. I know your users may not like the "yy/mm" format as column headings. But if you switch it to "mm/yy" you'll end up with problems when the crosstab query includes multiple years. The headings will sort like this:

01/03 - 01/04 - 02/03 - 02/04 - ... and so on.

I just helped a freind work out the correct sorting for this, but it wasn't easy. We had to create a string to insert into a SQL statement programmatically, that forced the "mm/yy" into the right order using the "Column Headings" property of the query. Then we had to use a programmatically created QryDef instead of DoCmd.RunSQL. It was ugly.

Okay, now for [tbl WEEKS]
Here's some sample data:

Cal Date Cal Year MonthStr Week No Last Date
1/21/2003 2003 03/01 4 1/23/2003
1/22/2003 2003 03/01 4 1/23/2003
1/23/2003 2003 03/01 4 1/23/2003
1/24/2003 2003 03/01 5 1/30/2003
1/25/2003 2003 03/01 5 1/30/2003
1/26/2003 2003 03/01 5 1/30/2003
1/27/2003 2003 03/01 5 1/30/2003
1/28/2003 2003 03/01 5 1/30/2003
1/29/2003 2003 03/01 5 1/30/2003
1/30/2003 2003 03/01 5 1/30/2003
1/31/2003 2003 03/01 6 2/6/2003
2/1/2003 2003 03/02 6 2/6/2003
2/2/2003 2003 03/02 6 2/6/2003
2/3/2003 2003 03/02 6 2/6/2003
2/4/2003 2003 03/02 6 2/6/2003
2/5/2003 2003 03/02 6 2/6/2003
2/6/2003 2003 03/02 6 2/6/2003
2/7/2003 2003 03/02 7 2/13/2003
2/8/2003 2003 03/02 7 2/13/2003

The LastDate column reflects the date on Thursday, which for my purposes is the last day of the (pay) week. Friday being the "first day" since we go by PayWeeks for a lot of stuff.

Anyway, Its not hard to create a file like this in Excel and import it into Access as a table.

In Excel put a date in the first row, first column. Then in the second row of the first column put: =A1 + 1

It should put a date in row 2 column 1 that is one day more than row 1 column 1.

If that works, then copy the formula from row 2 column 1 and paste it in every row (using the click and drag the mouse to highlight a few hundred rows, of course).

Once you have that listing of dates as long as you want (mine goes from 1995 through 2005), then use Paste Append to paste it into a new table in Access. Name the first field like my table, then add the other fields to the in design mode.

Define the field types as follows:

[Cal Date] As Date/Time
[Cal Year] As Number
[MonthStr] As Text

You don't really need the last 2 fields but if you want them, they are:

[Week No] As Number
[Last Date] As Date/Time

Now, create a new query and select the query type "Update Query"

In the query grid update the [Cal Year] field to:

DatePart("yyyy",[Cal Date])

Update the [MonthStr] field to:

Format([Cal Date],"yy/mm&quot

If you want the other two fields, the formula is:

[Week No] Update to:

DatePart("ww",[Cal Date])

[Last Day] Update to:

Hmmm.... Now that I think about it, I don't remember off had how I calculate the last day of the week (in my case, the last day of the pay week - Thursdays) But if you really need it, I'll see what I can come up with.

Anyway, name the table the same as mine, or give it your own name and replace my name with your name in the stuff I wrote before....

 

[lmn]

Thanks,

Do you know if there is a way to hard code it so that it is always picking up the current year worth of information? This client works on a different fiscal year (so their 1st month is Feb - probably doesn't matter).

I'm starting to think now that they may want to see a rolling 6 months and not just fiscal year tho - cause they are using this to allocate resources - and when it comes to the end of the year they will still want to see 6 mos out....another headache I will deal with tomorrow!

 

[ReluctantDataGuy]

I'm at home now and don't have access to the example I made, but I think the answer is yes to both the current fiscal year and to the rolling 6 months.

USE THE FOLLOWING METHOD FOR CURRENT FISCAL YEAR (FEB-JAN)

In the first query (not the crosstab) in my first note, go into design mode (not SQL mode).

Add the field [Cal Date] to the query grid.
Add a calculated field to the query grid as follows:

CurrDate: Date()

In the criteria field for CurrDate you'll add the following:

<CDate(&quot;02/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))

Directly below that in the OR row, add the following:

>=CDate(&quot;02/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))

Now, in the criteria field for [Cal Date] add the following:

Between CDate(&quot;01/31/&quot; & DatePart(&quot;yyyy&quot;,DateAdd(&quot;yyyy&quot;,-1,Date()))) And CDate(&quot;02/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))

And directly below that in the OR row, add the following:

Between CDate(&quot;01/31/&quot; & DatePart(&quot;yyyy&quot;,DateAdd(&quot;yyyy&quot;,1,Date()))) And CDate(&quot;02/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))

Make sure that neither field is &quot;checked&quot; - they are in the query for the WHERE condition only.

USE THE FOLLOWING FOR ROLLING 6 MONTHS

Add the field [Cal Date] to the query (not the crosstab one).

Make sure it is not &quot;Checked&quot;. You don't want it in the query result - its only there as part of the where clause.

In the criteria for {Cal Date], add the following:

Between DateAdd(&quot;m&quot;,-6,CDate(DatePart(&quot;m&quot;,Date()) & &quot;/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))) And CDate(DatePart(&quot;m&quot;,Date()) & &quot;/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))-1

Note: If you're in the middle of May, as we are, the above criteria won't show any data for the current month - since it's end date is the first of the current month minus one day (ie: the last day of the previous month - regardless of how many days are in that month - for example Feb can have 27 or 28 - this criteria doesn't care)

If you did want to include dates up until today, then it would be:

Between DateAdd(&quot;m&quot;,-6,CDate(DatePart(&quot;m&quot;,Date()) & &quot;/01/&quot; & DatePart(&quot;yyyy&quot;,Date()))) And Date()


WHEW!!! That was fun... I sure hope it works for you. At least you can look this stuff over carefully and figure out how to implement what you do need. If you try something else and you're having problems, pay especially close attention to how I've concatenated stuff here.