Autofil Fiscal Qtr number from a date

KLund1 · Jul 25, 2018

I'm back for more help.
I have an Access 2000 database form that has a field to enter the fiscal quarter number from a date. I currently look up the qtr number on a piece of paper.
I think to myself, why can't Access do this.
If I type in the date field, how can access look up and auto fill the qtr number field?
Below is a sample of my piece of paper.
Here is an example; If I type in 9/15/13 for a date, the qtr field should autofill with 201402.
How could this be done? Please remember, I am Aew to access, but know a lot about Excel. Thanks for any input or suggestions.

Beginning Date End Date Qtr number
4/29/2013 7/28/2013 201401
7/29/2013 10/27/2013 201402
10/28/2013 1/26/2014 201403
1/27/2014 4/27/2014 201404
4/28/2014 7/27/2014 201501
7/28/2014 10/26/2014 201502
10/27/2014 1/25/2015 201503
1/26/2015 4/26/2015 201504
4/27/2015 7/26/2015 201601
7/27/2015 10/25/2015 201602
10/26/2015 1/23/2016 201603
1/24/2016 4/30/2016 201604
5/1/2016 7/30/2016 201701
7/31/2016 10/29/2016 201702
10/30/2016 1/28/2017 201703
1/29/2017 4/29/2017 201704

isladogs · Jul 25, 2018

Access can determine the quarter using the DatePart function.
See https://www.techonthenet.com/access/functions/date/datepart.php

For example:
DatePart("q",#9/15/2013#)=3 because it's the third quarter of 2013

Or Datepart("yyyy",#9/15/2013#) & "0" & Datepart("q",#9/15/2013#) =201303

However you aren't using standard quarter notation.
I think yours are related to financial years.
Unless there's a function I'm unaware of, you'll need to create your own function

June7 · Jul 25, 2018

Your quarter begin and end dates are different each year. Never seen that before. Build a table that replicates your paper. Code can do the 'lookup' you do by eyeball. Can use DLookup() domain aggregate function.

KLund1 · Jul 25, 2018

Thanks everyone, Very much appreciated for the quick responses!
Dlookup looks like it might work, but I'm unsure as to how to setup the parameters.
I made a quick DB1 with my dates, and a small form with a date and fiscal qtr fields
I added an After Update event expression with dlookup for the Date filed1, but it is obviously wrong. I attached it here. Could you make the changes that would make this work?

isladogs · Jul 26, 2018

Here is a corrected version of my original reply (which has now been deleted)

Code:

Private Sub Field2_AfterUpdate()
    Field1 = DLookup("[Qtr number]", "Sheet1", "[Beginning Date]<= #" & Format(Field2, "mm/dd/yyyy") & "# And [End Date]>= #" & Format(Field2, "mm/dd/yyyy") & "#")
End Sub

That should now work correctly. I hope!

I strongly recommend you:
a) Rename Field1, Field2, Sheet1, Table1 with meaningful names
b) Get rid of spaces in field names - e.g. use EndDate instead of End Date
c) don't use the same name for tables and forms

MarkK · Jul 26, 2018

If the pattern is that the start date of the period is the last monday of the month, and offset by one month from actual calendar quarters, then you should be able to calculate them, and avoid the DLookup() option.

Is that the rule you use to determine the start date?

Mark

MarkK · Jul 26, 2018

Here's a class you can use that encapsulates your quarter logic, I think, if you can figure out how to use it.

Code:

[SIZE="1"]Option Compare Database
Option Explicit

Private m_date As Date

[COLOR="Green"]'******************************************************** Jul 25 2018 *****
'
'   Property
'
'**************************************************************************[/COLOR]
Property Get StartDate() As Date
    StartDate = PreviousMonday
End Property

Property Get EndDate() As Date
[COLOR="green"]'   EndDate = StartDate - 1 of the next quarter, so...[/COLOR]
    Dim tmp As Date
    Dim c As New cMyCustomQuarter
    
    tmp = DateAdd("q", 1, m_date)       'get next quarter
    EndDate = c.Load(tmp).StartDate - 1 'get startdate - 1
End Property

Property Get QuarterNumber() As Long
    Dim tmp As Date
    tmp = PreviousMonday                            [COLOR="green"]'get the start date[/COLOR]
    tmp = DateSerial(Year(tmp), Month(tmp) + 9, 1)  [COLOR="green"]'add the offset[/COLOR]
    QuarterNumber = Format(tmp, "yyyy0q")          [COLOR="green"] 'format the output[/COLOR]
End Property

[COLOR="green"]'******************************************************** Jul 25 2018 *****
'
'   Private Property
'
'**************************************************************************[/COLOR]
Private Property Get PreviousMonday() As Date
    Dim tmp As Date
    tmp = LastDayOfStartMonth
    PreviousMonday = tmp - Weekday(tmp) + 2
End Property

Private Property Get LastDayOfStartMonth() As Date
    LastDayOfStartMonth = DateSerial(Year(m_date), StartMonth + 1, 0)
End Property

Private Property Get StartMonth() As Integer
    StartMonth = 3 * (DatePart("q", m_date) - 1) + 1
End Property

[COLOR="green"]'******************************************************** Jul 25 2018 *****
'
'   Method
'
'**************************************************************************[/COLOR]
Function Load(d1 As Date) As cMyCustomQuarter
[COLOR="green"]'   this is the initialization step for this class[/COLOR]
    m_date = d1
    Set Load = Me
End Function

Function ToString() As String
    ToString = _
        "Start.: " & Me.StartDate & vbCrLf & _
        "End...: " & Me.EndDate & vbCrLf & _
        "Number: " & Me.QuarterNumber
End Function[/SIZE]

Copy that code into a new class and call it cMyCustomQuarter. Then you can run code like this to test it...

Code:

Sub Test928346912487()
    With New cMyCustomQuarter
        .Load #9/15/2013#
        Debug.Print .StartDate, .EndDate, .QuarterNumber
    End With
End Sub

...or the same thing expressed slightly differently...

Code:

Sub Test928346912488()
    Dim tmp As New cMyCustomQuarter
    tmp.Load #9/15/2013#
    Debug.Print tmp.ToString
End Sub

Also, you can just hack the logic out of it and write it as a function, but this is a cool demo of how to use a class, since the problem wants us to expose the startdate, enddate, AND quarternumber as properties when we are given a particular date.

hth
Mark

isladogs · Jul 26, 2018

Many thanks to Mark for providing a good example of a class module solution

For completeness, here's a similar version using functions in a standard module.
I have just adapted Mark's code for each function rather than reinvent the wheel

Code:

Option Compare Database
Option Explicit

Function GetPreviousMonday(dteDate As Date) As Date
    Dim tmp As Date
    tmp = GetLastDayOfStartMonth(dteDate)
    GetPreviousMonday = tmp - Weekday(tmp) + 2
End Function

Function GetLastDayOfStartMonth(dteDate As Date) As Date
    GetLastDayOfStartMonth = DateSerial(Year(dteDate), GetStartMonth(dteDate) + 1, 0)
End Function

Function GetStartMonth(dteDate As Date) As Integer
    GetStartMonth = 3 * (DatePart("q", dteDate) - 1) + 1
End Function

Function GetQuarterNumber(dteDate As Date) As Long
    Dim tmp As Date
    tmp = GetPreviousMonday(dteDate)                  [COLOR="Green"]'get the start date[/COLOR]
    tmp = DateSerial(Year(tmp), Month(tmp) + 9, 1) [COLOR="green"] 'add the offset[/COLOR]
    GetQuarterNumber = Format(tmp, "yyyy0q")       [COLOR="green"] 'format the output[/COLOR]
End Function

Test the code using:

Code:

Sub TestQuarterNumber()
   Debug.Print GetQuarterNumber(#9/15/2013#)    
End Sub

KLund1 · Jul 26, 2018

Thanks everyone. A lot to digest. I will probably have questions in a few days.
Until I fully grasp everything above, I'll just use Ridder's solution. It works, it's quick, and I understand about 90% of it. The advise was good to know too.
Thanks again everyone!

isladogs · Jul 26, 2018

You're welcome.
Not sure which solution you mean, but I prefer the second one which means the lookup table isn't needed.
If you're using that one, thanks should really go to Mark whose code I plagiarised massively.

KLund1 · Aug 29, 2018

Ok, back to this.
They finally build me a faster machine.
It has Access XP(2002) not 2000 installed.
When I enter data in the field what was working from above I get an error.
Please see attached pic.
I did some quick googling and checked the VBA References between the old machine and the new machine and they are now the same.
After I changed the Ref's check boxes, I closed and opened again. I get the same error
from the Format command.
Suggestions on a fix?
Thanks

isladogs · Aug 29, 2018

At this rate you should get Access 2016 in about 2036....

Almost certainly a VBA reference issue.
Please post a screenshot of the references window

MarkK · Aug 29, 2018

Sometimes you just need to arbitrarily re-order the references. Pick one low in the list, hit the up-arrow so it moves up, and click OK. Then try your code again.
hth
Mark

KLund1 · Aug 29, 2018

MarkK - tried moving a couple references around - no change
ridders - pic attached, and yes 2036 is probably about right.

(small aside, my new machine is Core Quad 9850, running XP Pro. I had to source the parts myself, before IT would build it, as they can't find New XP compatible motherboards at Newegg. The network is so tight, I do not have a start button or desktop. just an app launcher with only the allowed programs to run. This makes dealing with workstation PC issues difficult at best. About 1/2 the office PC's are now up to Win7. I can't use Win7 as the 16-bit software I have to use dose not run on it! I feel like I work in 1992)

isladogs · Aug 29, 2018

KLund1 said:
MarkK - tried moving a couple references around - no change
ridders - pic attached, and yes 2036 is probably about right.

(small aside, my new machine is Core Quad 9850, running XP Pro. I had to source the parts myself, before IT would build it, as they can't find New XP compatible motherboards at Newegg. The network is so tight, I do not have a start button or desktop. just an app launcher with only the allowed programs to run. This makes dealing with workstation PC issues difficult at best. About 1/2 the office PC's are now up to Win7. I can't use Win7 as the 16-bit software I have to use dose not run on it! I feel like I work in 1992)

As you can see, you have a missing reference to utility.mda.
That's an Access add-in of some sort which dates back to Access 95 or thereabouts - perhaps its no longer needed. Untick then do a compact & repair & see what happens

Otherwise, you could try & find it if possible so it is no longer missing.
As I don't have XP I can't say what you should replace it with if that fails

Otherwise a google search for' utility.mda broken reference' will give lots of hits e.g. https://answers.microsoft.com/en-us/office/forum/office_2007-access/error-when-open-access2007-based-database/41938883-e57e-40ca-a733-9cb27d837909?db=5&auth=1

As for your 16-bit programs stopping you using Win 7, why not run those in compatibility mode in Win 7.
The last one I remember was the old Borland Data Engine (BDE) and I remember getting that to work in Win7
Alternatively, run Win 7 for the 32-bit apps with a virtual '1992' machine for the 16-bit apps.

KLund1 · Aug 30, 2018

When I try to recompile the code I get a new error.
see pic.
Suggestions?
Oh, again many thanks for all your input and help

MarkK · Aug 30, 2018

And you unchecked that other missing reference?
Mark

KLund1 · Aug 30, 2018

I unchecked "Utility.mda", but not "MISSING Utility.mda"
I unchecked that and it recompiled without errors and all is working now.
All good for now.
Thanks for all the help!
Learning as I go...

isladogs · Aug 30, 2018

If it compiles despite having the MISSING reference, remove that also if not already done

Autofil Fiscal Qtr number from a date

Registered User.

MVP / VIP

AWF VIP

Registered User.

Attachments

MVP / VIP

Attachments

bit cruncher

bit cruncher

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

bit cruncher

Registered User.

Attachments

MVP / VIP

Registered User.

Attachments

bit cruncher

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread