date calculated field

[bimmer5]

I need to insert a calculated field in my query that will for each calendar date record show which fiscal month the date belongs to. The START DATE for each fiscal month is the the first Saturday after the last Friday in the previous calendar month and the END DATE is the last Friday in the current calendar month. For example:
Calendar Fiscal
01/25/08 Jan 08
01/26/08 Feb 08
02/29/08 Feb 08
03/01/08 Mar 08
03/02/08 Mar 08 etc…

Attached, please find a sample of dates in the database.
Any suggestion how to create the calculated Fiscal Month query is appreciated.
Thank you in advance.
Peter

 

[MSAccessRookie]

I think you will need a combination of the following functions:

DateSerial
Year
Month
Date
WeekDay
Format

The following query uses these functions to show the calendar month start and end dates as well as the Fiscal Month Start and End dates for the current Month. I hope that it gives you a push in the right direction.

select Format(DateSerial(Year(Date())-0,Month(Date()),1), 'dddd') AS CalendarStartDay,
DateSerial(Year(Date())-0,Month(Date()),1) AS CalendarStartDate,
Format(DateSerial(Year(Date())-0,Month(Date())+1,0), 'dddd') AS CalendarEndDay,
DateSerial(Year(Date())-0,Month(Date())+1,0) AS CalendarEndDate,
Format(DateSerial(Year(Date())-0,Month(Date()),1-(7-WeekDay(DateSerial(Year(Date())-0,Month(Date()),1)))), 'dddd') AS FiscalStartDay,
DateSerial(Year(Date())-0,Month(Date()),1-(7-WeekDay(DateSerial(Year(Date())-0,Month(Date()),1)))) AS FiscalStartDate,
Format(DateSerial(Year(Date())-0,Month(Date())+1,(7-WeekDay(DateSerial(Year(Date())-0,Month(Date()),1))))-2, 'dddd') AS FiscalEndDay,
DateSerial(Year(Date())-0,Month(Date())+1,(7-WeekDay(DateSerial(Year(Date())-0,Month(Date()),1))))-2 AS FiscalEndDate
From {YourTable};

 

[raskew]

Hi -

Although written for a different purpose, this should give you what you need.

Copy/paste this to a standard module:

Function NthXDay(pdate As Variant, _
                 pWDay As Integer, _
                 pIncrement As Integer) As Date
'***************************************************************
'Purpose:   Some holidays fall on the Nth XDay of the month.
'           Given a month, year, weekday and increment (e.g.
'           1st, 3rd, last) this function returns the specific
'           date.
'Coded by:  raskew
'Inputs:    ? NthXDay(dateserial(year(date), 11, 1),vbMonday, 3)
'Output:    Specified date
'Note:      Use 6 to indicate the last increment, since no month
'           will have 6 of any specified weekday.
'***************************************************************

Dim dteDate As Date
Dim newDate As Date

    dteDate = DateValue(pdate)
    
    'adjust the increment if it's more than 6
    pIncrement = IIf(pIncrement > 6, 6, pIncrement)
  
    'determine first day of given month
    dteDate = DateSerial(year(dteDate), month(dteDate), 1)
    
    'determine first specified day of given month, e.g. vbSunday
    newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)
    
    'determine the nth specified day of given month
    newDate = DateAdd("d", 7 * (pIncrement - 1), newDate)
    
    'if the resulting calculation is greater than the length of the
    'specified month, cycle backwards to the last specified day of
    'the month
    Do While month(newDate) <> month(dteDate)
       newDate = newDate - 7
    Loop
    
    NthXDay = newDate

End Function

Then, to test from the Immediate (debug) window:

dteDate = #3/1/08#

? "Fiscal Month for " & format(dteDate, "mmm yy")
Fiscal Month for Mar 08

'start date - last Saturday of previous month
? nthxday(dateadd("m", -1, dteDate),7,6)
2/23/2008

'end date - last Friday of current month
? nthxday(dteDate, 6,6)
3/28/2008

HTH - Bob

 

[bimmer5]

MSAccessRookie,

thank you very much for your reply. Unfortunatelly, the resulting eight calculated fields returned are not what I was looking for. I truly appologize if I didn't explain well; the requirement is to have only one calculated field that will return one of the following values, for instance:
Jan 08
Feb 08
Mar 08
Apr 08, etc..
I couldn't format my initial post so that you can see the two separated fields. The second "Fiscal Month" field should contain one of the above values. Please do not get confuse by the days that I inserted between the two fields (they are just for reference only).
Thank you.

 

[raskew]

Returning to the scene of the crime, the function posted earlier should provide the fiscal month.

dteDate = #3/30/08#
FiscalMonth = format(iif(dteDate >= nthxday(dteDate, 7,6), dateadd("m", 1, dteDate), dteDate), "mmm yy")
? fiscalmonth
Apr 08

dteDate = #3/28/08#
FiscalMonth = format(iif(dteDate >= nthxday(dteDate, 7,6), dateadd("m", 1, dteDate), dteDate), "mmm yy")
? fiscalmonth
Mar 08

HTH - Bob

 

[bimmer5]

Bob,

I don't know if I am doing something wrong but I keep getting error messages after I post the codes above. Do you mind pasting the codes by yourself into the sample I provided earlier and uploading it here, please?
One more thing; the code needs to resolve any fiscal month for any year (not only for these 3-4 months I posted here).
I hope this further clarifies the requirement here.
Tx,
Peter

 

[raskew]

Hi -

First of all, DATE is a reserved word in Access and should not be used as a field or variable name.

I changed the table to tblDates and the field to MyDate. Copied in the function and created a calculated field in the query.

Give it a try -- seems to work as advertised. It's not year or month specific -- should work with any date.

Bob

 

[bimmer5]

Bob,

we are almost there! I checked with the accountant and found a little glitch (my fault!). The START DATE should be the next Saturday after the last Friday in the previous month. That Saturday could be in the current month, too. For example, in the sample you sent me, the calendar date 02/23/08 should belong to Feb 08 (and not to Mar 08).
I honestly appologize for wasting your time.
Is there easy way to fix the code, please?
Thank you.

 

[raskew]

OK -

Replace the calculated field with:

FiscalMonth: Format(Switch([mydate]<=nthxday([mydate],6,6),[mydate],[mydate]>=(nthxday([mydate],6,6)+1) And Month([mydate])=Month(nthxday([mydate],6,6)+1),DateSerial(Year([mydate]),Month([mydate])+1,1),True,nthxday([mydate],6,6)+1),"mmm yy")

This accounts for the three possible scenarios:
MyDate >= Next Saturday after the last Friday
-- and --
1) Next Saturday after the last Friday is in the current calendar month.
2) Next Saturday after the last Friday is in the following calendar month
-- or --
3) MyDate is less than the Next Saturday after the last Friday of the current month.

HTH - Bob

Added: To make it a little easier to read from the monitor:

Format(Switch([mydate]<=nthxday([mydate],6,6),[mydate],
[mydate]>=(nthxday([mydate],6,6)+1) And Month([mydate])=Month(nthxday([mydate],6,6)+1),
DateSerial(Year([mydate]),Month([mydate])+1,1),
True,nthxday([mydate],6,6)+1),"mmm yy")

 

[bimmer5]

Bob,

you got it!!!
That's it, that is what I needed, you the man!
Works like a charm, I can't thank you enough!
Thanks to all who participated in this thread, very special thanks to Bob!
All the best to you guys,
Peter

 

[raskew]

Don't you just love it when a plan comes together?!

Best Wishes - Bob

 

[bimmer5]

Bob,

heh, the celebration has been short lived. The code worked fine in the sample but in my real database I keep getting the following error:
"Undefined function 'nthxday' in expression".
I googled around, some said to check broken references but it didn't work. Apparently, the 'nthxday' is not a standard function, I don't know?
Any idea?
Tx,
Peter

 

[raskew]

Nthxday() is a function I created, i.e. a User-Defined Function (UDF) , not a 'standard' Access function.

You'll need to copy it from the sample database (or Post #3, above) into your working database.

Bob

 

[bimmer5]

Bob,
I really feel lucky you replied to my post today. This is a science to me, you must be some kind of genious?
It worked as suggested, thanks million times!

 

[Xx_TownDawg_xX]

user-defined function is new to me.. but I'm searching now so I can get this solution in place in my access project.

 

[Xx_TownDawg_xX]

Ok, I've got the user-defined function working now. One question, since different companies have different fiscal calendars (I'm assuming), where do I tell my function where the start date of OUR fiscal date is.. ?..

Dim dteDate As Date
Dim newDate As Date

dteDate = DateValue(pdate)

'adjust the increment if it's more than 6
pIncrement = IIf(pIncrement > 6, 6, pIncrement)

'determine first day of given month
dteDate = DateSerial(Year(dteDate), Month(dteDate), 1)

'determine first specified day of given month, e.g. vbSunday
newDate = dteDate - Weekday(dteDate) + pWDay + IIf(Weekday(dteDate) > pWDay, 7, 0)

'determine the nth specified day of given month
newDate = DateAdd("d", 7 * (pIncrement - 1), newDate)

'if the resulting calculation is greater than the length of the
'specified month, cycle backwards to the last specified day of
'the month
Do While Month(newDate) <> Month(dteDate)
newDate = newDate - 7
Loop

NthXDay = newDate


Thank you raskew, though, because it is working fine, once I understand it a little better.

What I've got right now is your EXACT module pasted as an "unrelated object" and named "fiscal"

and a calculated field that states, "=Format(Switch([Audit_Date]<=nthxday([Audit_Date],6,6),[Audit_Date],[Audit_Date]>=(nthxday([Audit_Date],6,6)+1) And Month([Audit_Date])=Month(nthxday([Audit_Date],6,6)+1),DateSerial(Year([Audit_Date]),Month([Audit_Date])+1,1),True,nthxday([Audit_Date],6,6)+1),"mmm dd yy")"

My table lookup is keyed on the appropriate "first day" of the fiscal month, meaning that 24Dec08 (12/24/08) would be calculated as 01Jan09 (01/01/09).

 

[Xx_TownDawg_xX]

Sorry, finally got it all figured out I think.

 

[Xx_TownDawg_xX]

OK, I'm really close. Just a little tweaking.

bimmer5 originally stated "The START DATE for each fiscal month is the the first Saturday after the last Friday in the previous calendar month and the END DATE is the last Friday in the current calendar month."

I'm at home, and not at work, but I'm thinking mine is actually -- "The START DATE for each fiscal month is the the first MONDAY before the last Friday in the previous calendar month and the END DATE is the last SUNDAY in the current calendar month."

So just a little tweaking, and I'm golden. Can you point me to the best way to edit your response, raskew?

I'll double check this when I get back to work on Monday -- and give you some examples to be sure.

 

[Xx_TownDawg_xX]

Greetings.

I have attached a pdf of our fiscal months. For example, Jan-09 started on 12/22/08. I love all the VBA/formula work contributed, and have incorporated all of it into my current project -- unfortunately I do not understand all of it well enough to modify it for our fiscal calendar.

Can someone help me out? Thanks !

 

[raskew]

"The START DATE for each fiscal month is the the first Saturday after the last Friday in the previous calendar month and the END DATE is the last Friday in the current calendar month."

I've got to ask -- where do you come up with this weird crap? Is there some logic that I'm missing? My suggestion is that you go back to the folks who dreamed up this scheme and have them describe how to do it. Bet they'll be lost.

Bob