Display Accounting Month for a Date

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

I have a given date and I would like Access to display or
calculate the accounting month for that given date. The
accounting month ends on the last Friday of the month and
the next month begins on that following Saturday. Is this
possible with Access?

I have coded for counting only working days (leaving out
Sat and Sun) for another application but this code for the
last Friday of a month stuff has got me stumped. Any info
would be great.

-Patrick
 
One solution might be to create a separate table listing the start and end
date of each accounting month, and then use a query to look up the
accounting month from this table based on the date.

For example, you might have a table (say, named "Accounting Months") that
looks something like this:

Accounting Month, From Date, Through Date
2003-01, 01/01/2003, 01/31/2003
2003-02, 02/01/2003, 02/28/2003
2003-03, 03/01/2003, 03/28/2003
2003-04, 03/29/2003, 04/25/2003
2003-05, 04/26/2003, 05/30/2003
2003-06, 05/31/2003, 06/27/2003
2003-07, 06/28/2003, 07/25/2003
2003-08, 07/26/2003, 08/29/2003
2003-09, 08/30/2003, 09/26/2003
2003-10, 09/27/2003, 10/31/2003
2003-11, 11/01/2003, 11/28/2003
2003-12, 11/29/2003, 12/31/2003
2004-01, 01/01/2004, 01/31/2004
2004-02, 02/01/2004, 02/27/2004
2004-03, 02/28/2004, 03/26/2004
2004-04, 03/27/2004, 04/30/2004
2004-05, 05/01/2004, 05/28/2004
2004-06, 05/29/2004, 06/25/2004
..
..
..

You might then look up a given date in a table using a query something like
this:

SELECT
[Your Table].*,
[Accounting Months].[Accounting Month]
FROM
[Your Table],
[Accounting Months]
WHERE
[Your Table].[Your Date Field] >= [Accounting Months].[From Date]
AND
[Your Table].[Your Date Field] <= [Accounting Months].[Through Date]

This assumes "Your Date Field", "From Date", and "Through Date" don't
contain any time of day component.
 
Thanks for the info. I had originally thought of doing
something similar but wanted to avoid building a table
that would require updating. I guess there is no way
around it.

Thank again,
Patrick

-----Original Message-----
One solution might be to create a separate table listing the start and end
date of each accounting month, and then use a query to look up the
accounting month from this table based on the date.

For example, you might have a table (say,
Click to expand...
named "Accounting Months") that
looks something like this:

Accounting Month, From Date, Through Date
2003-01, 01/01/2003, 01/31/2003
2003-02, 02/01/2003, 02/28/2003
2003-03, 03/01/2003, 03/28/2003
2003-04, 03/29/2003, 04/25/2003
2003-05, 04/26/2003, 05/30/2003
2003-06, 05/31/2003, 06/27/2003
2003-07, 06/28/2003, 07/25/2003
2003-08, 07/26/2003, 08/29/2003
2003-09, 08/30/2003, 09/26/2003
2003-10, 09/27/2003, 10/31/2003
2003-11, 11/01/2003, 11/28/2003
2003-12, 11/29/2003, 12/31/2003
2004-01, 01/01/2004, 01/31/2004
2004-02, 02/01/2004, 02/27/2004
2004-03, 02/28/2004, 03/26/2004
2004-04, 03/27/2004, 04/30/2004
2004-05, 05/01/2004, 05/28/2004
2004-06, 05/29/2004, 06/25/2004
..
..
..

You might then look up a given date in a table using a query something like
this:

SELECT
[Your Table].*,
[Accounting Months].[Accounting Month]
FROM
[Your Table],
[Accounting Months]
WHERE
[Your Table].[Your Date Field] >= [Accounting Months]. [From Date]
AND
[Your Table].[Your Date Field] <= [Accounting Months]. [Through Date]

This assumes "Your Date Field", "From Date", and "Through Date" don't
contain any time of day component.


Patrick said:
I have a given date and I would like Access to display or
calculate the accounting month for that given date. The
accounting month ends on the last Friday of the month and
the next month begins on that following Saturday. Is this
possible with Access?

I have coded for counting only working days (leaving out
Sat and Sun) for another application but this code for the
last Friday of a month stuff has got me stumped. Any info
would be great.

-Patrick
Click to expand...


.
Click to expand...
 
I have a given date and I would like Access to display or
calculate the accounting month for that given date. The
accounting month ends on the last Friday of the month and
the next month begins on that following Saturday. Is this
possible with Access?
Click to expand...

Try:

Format( _
dtStart + (Weekday( _
DateSerial(Year(dtStart), Month(dtStart) + 1, 0 ) _
+ 1) Mod 7), _
"yyyy-mm")

If possible, it might be easier to break the whole thing out into a
function:

Public Function AccountingMonth(SomeDate As Date) As String

Dim w As Integer
Dim dtStart As Date
Dim dtTemp As Date

Dim wWeekDay As Integer
Dim wDaysLeft As Integer

' get last day of month
dtTemp = DateSerial(Year(SomeDate), Month(SomeDate) + 1, 0)

' get the number of days between the last Friday and the end
wDaysLeft = (Weekday(dtTemp) + 1) Mod 7

' Add those days to the date, and get what month that is in
AccountingMonth = Format$(SomeDate + wDaysLeft, "yyyy-mm")

End Function


Hope that helps


Tim F
 
Dang you have to really watch where you post your email.
About 15 minutes I posted first with email addy I got a
update email from micosoft with NetPatch in subject and
attached file Pack478.exe

Well I have another question based on the table setup you
provided below. How can a return that value of
AccountingMonth to a field in a query for a record.

Thanks
-----Original Message-----
One solution might be to create a separate table listing the start and end
date of each accounting month, and then use a query to look up the
accounting month from this table based on the date.

For example, you might have a table (say,
Click to expand...
named "Accounting Months") that
looks something like this:

Accounting Month, From Date, Through Date
2003-01, 01/01/2003, 01/31/2003
2003-02, 02/01/2003, 02/28/2003
2003-03, 03/01/2003, 03/28/2003
2003-04, 03/29/2003, 04/25/2003
2003-05, 04/26/2003, 05/30/2003
2003-06, 05/31/2003, 06/27/2003
2003-07, 06/28/2003, 07/25/2003
2003-08, 07/26/2003, 08/29/2003
2003-09, 08/30/2003, 09/26/2003
2003-10, 09/27/2003, 10/31/2003
2003-11, 11/01/2003, 11/28/2003
2003-12, 11/29/2003, 12/31/2003
2004-01, 01/01/2004, 01/31/2004
2004-02, 02/01/2004, 02/27/2004
2004-03, 02/28/2004, 03/26/2004
2004-04, 03/27/2004, 04/30/2004
2004-05, 05/01/2004, 05/28/2004
2004-06, 05/29/2004, 06/25/2004
..
..
..

You might then look up a given date in a table using a query something like
this:

SELECT
[Your Table].*,
[Accounting Months].[Accounting Month]
FROM
[Your Table],
[Accounting Months]
WHERE
[Your Table].[Your Date Field] >= [Accounting Months]. [From Date]
AND
[Your Table].[Your Date Field] <= [Accounting Months]. [Through Date]

This assumes "Your Date Field", "From Date", and "Through Date" don't
contain any time of day component.


Patrick said:
I have a given date and I would like Access to display or
calculate the accounting month for that given date. The
accounting month ends on the last Friday of the month and
the next month begins on that following Saturday. Is this
possible with Access?

I have coded for counting only working days (leaving out
Sat and Sun) for another application but this code for the
last Friday of a month stuff has got me stumped. Any info
would be great.

-Patrick
Click to expand...


.
Click to expand...
 
Using query whose SQL looks like the SQL I suggested in my original post.

If you're not familiar with SQL, you might try something like this:

1. Edit the SQL from my original post to replace the table and field names
I assumed (in square brackets) with your actual ones.

2. Create new query in design view.

3. In query design view, chose SQL View from the View menu.

4. Replace the SQL that appears with the SQL you edited in Step 1.

5. From the View menu, choose Design View to see the graphical equivalent
of the SQL. This might be more meaningful to you.

Patrick said:
Dang you have to really watch where you post your email.
About 15 minutes I posted first with email addy I got a
update email from micosoft with NetPatch in subject and
attached file Pack478.exe

Well I have another question based on the table setup you
provided below. How can a return that value of
AccountingMonth to a field in a query for a record.

Thanks
-----Original Message-----
One solution might be to create a separate table listing the start and end
date of each accounting month, and then use a query to look up the
accounting month from this table based on the date.

For example, you might have a table (say,
Click to expand...
named "Accounting Months") that
looks something like this:

Accounting Month, From Date, Through Date
2003-01, 01/01/2003, 01/31/2003
2003-02, 02/01/2003, 02/28/2003
2003-03, 03/01/2003, 03/28/2003
2003-04, 03/29/2003, 04/25/2003
2003-05, 04/26/2003, 05/30/2003
2003-06, 05/31/2003, 06/27/2003
2003-07, 06/28/2003, 07/25/2003
2003-08, 07/26/2003, 08/29/2003
2003-09, 08/30/2003, 09/26/2003
2003-10, 09/27/2003, 10/31/2003
2003-11, 11/01/2003, 11/28/2003
2003-12, 11/29/2003, 12/31/2003
2004-01, 01/01/2004, 01/31/2004
2004-02, 02/01/2004, 02/27/2004
2004-03, 02/28/2004, 03/26/2004
2004-04, 03/27/2004, 04/30/2004
2004-05, 05/01/2004, 05/28/2004
2004-06, 05/29/2004, 06/25/2004
..
..
..

You might then look up a given date in a table using a query something like
this:

SELECT
[Your Table].*,
[Accounting Months].[Accounting Month]
FROM
[Your Table],
[Accounting Months]
WHERE
[Your Table].[Your Date Field] >= [Accounting Months]. [From Date]
AND
[Your Table].[Your Date Field] <= [Accounting Months]. [Through Date]

This assumes "Your Date Field", "From Date", and "Through Date" don't
contain any time of day component.


Patrick said:
I have a given date and I would like Access to display or
calculate the accounting month for that given date. The
accounting month ends on the last Friday of the month and
the next month begins on that following Saturday. Is this
possible with Access?

I have coded for counting only working days (leaving out
Sat and Sun) for another application but this code for the
last Friday of a month stuff has got me stumped. Any info
would be great.

-Patrick
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top