assigning a month based on date

[H0MELY]

Greetings, thank you very much for looking at my post. I am hoping that
someone will be able to help me assign a month value to an invoice based upon
its period_begin_date and period_end_date. Assigning the month is easy when
the the two dates occur in teh same month (1/1/08 to 1/31/08) but the problem
I hope you can solve for me is when the begin and end dates are in two
seperate months (4/28/08 to 5/4/08).

What determines the month of the invoice on dates such as 4/28/08 to 5/4/08
is the number of weekdays in each month. So for an invoice dated as above it
would be considered an April Invoice...4/28 (Mon), 4/29 (Tue), 4/30 (Wed),
5/1 (Thur) and 5/2 (Fri)...or 3 weekdays in April and only 2 weekdays in May.

So here is where I am getting stuck...I need to automate this process so
that when the invoice summary (vendor_name, Invoice_number,
period_begin_date, period_end_date and invoice_total) is imported into access
and I assign a Billing Month to the data...those invoices that span 2 months
are assigned to the correct month.

Currently I am assigning a month to both the begin and end dates and then
doing an Iif statement comparison to identify all of the invoices contained
within a month (begin and end date are in the same month and year) I then
manually adjust the invoices that are not contained within a singular month.
This works...but is time consuming.

Any guidance would be VERY VERY VERY appreciated. Thanks again to everyone
for looking.

-John

 

[Jeff Boyce]

And what do you do when there's the same number of work days in each month?

Would it simplify your task any if you just used the EndDate's month every
time?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[H0MELY]

Unfortunately using the end date's month is not an option because of
budgeting and forcasting requirements.

Almost all of the invoices that cause the issue are Sunday to Saturday
invoices...meaning that there should never be an issue where the number of
weekdays is the same between months. But if if on the outside chance that
that does happen...I guess I will initiate a manual update during calidation.

The biggest thing right now is creating something that will recognize the
number of workdays and assign a month based upon it.

Thanks very much for your help =)

 

[Jeff Boyce]

I've not run across a requirement like this before, so I don't have a
solution in mind. Perhaps other newsgroup readers can offer an approach.

It occurs to me that you'd need to calculate the number of workdays between
the start date and the end date if the "month" of both is the same, but the
number of workdays between the start date and the end-of-month, and the
beginning-of-month and the end date if the months don't match. Seems to me
you're looking at creating a procedure to handle this.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

I did it with a bunch of queries but it could be done with subqueries a lot
simpler. Here are the queries based on table named Homely. This process
requires a table named CountNumber with field CountNUM containing numbers
from 0 (zero) through 31.

HOMELY_Begin --
SELECT HOMELY.InvoiceID, HOMELY.period_begin_date,
Format(DateAdd("d",[CountNUM],[period_begin_date]),"w") AS Weekdays
FROM HOMELY, CountNumber
WHERE (((Format(DateAdd("d",[CountNUM],[period_begin_date]),"w")) Between 2
And 6) AND
((DateAdd("d",[CountNUM],[period_begin_date]))<=DateSerial(Year([period_begin_date]),Month([period_begin_date])+1,0)))
ORDER BY HOMELY.InvoiceID;

HOMELY_End --
SELECT HOMELY.InvoiceID, HOMELY.period_end_date,
Format(DateAdd("d",[CountNUM],DateSerial(Year([period_End_date]),Month([period_End_date]),1)),"w") AS Weekdays
FROM HOMELY, CountNumber
WHERE
(((Format(DateAdd("d",[CountNUM],DateSerial(Year([period_End_date]),Month([period_End_date]),1)),"w"))
Between 2 And 6) AND
((DateAdd("d",[CountNUM],DateSerial(Year([period_End_date]),Month([period_End_date]),1)))<=[period_End_date]))
ORDER BY HOMELY.InvoiceID;

HOMELY_Begin_Count --
SELECT HOMELY_Begin.InvoiceID, HOMELY_Begin.period_begin_date,
Count(HOMELY_Begin.Weekdays) AS CountOfWeekdays
FROM HOMELY_Begin
GROUP BY HOMELY_Begin.InvoiceID, HOMELY_Begin.period_begin_date;

HOMELY_End_Count --
SELECT HOMELY_End.InvoiceID, HOMELY_End.period_end_date,
Count(HOMELY_End.Weekdays) AS CountOfWeekdays
FROM HOMELY_End
GROUP BY HOMELY_End.InvoiceID, HOMELY_End.period_end_date;

SELECT HOMELY.InvoiceID, HOMELY.period_begin_date, HOMELY.period_end_date,
IIf(Month([HOMELY].[period_end_date])=Month([HOMELY].[period_begin_date]),Month([HOMELY].[period_begin_date]),IIf([HOMELY_Begin_Count].[CountOfWeekdays]>[HOMELY_End_Count].[CountOfWeekdays],Month([HOMELY].[period_begin_date]),Month([HOMELY].[period_End_date]))) AS Invoice_Month
FROM (HOMELY INNER JOIN HOMELY_Begin_Count ON (HOMELY.period_begin_date =
HOMELY_Begin_Count.period_begin_date) AND (HOMELY.InvoiceID =
HOMELY_Begin_Count.InvoiceID)) INNER JOIN HOMELY_End_Count ON
(HOMELY.period_end_date = HOMELY_End_Count.period_end_date) AND
(HOMELY.InvoiceID = HOMELY_End_Count.InvoiceID);