Autofil Fiscal Qtr number from a date (1 Viewer)

KLund1

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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

AWF VIP
Local time
Today, 00:55
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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?
 

Attachments

  • db1.mdb
    184 KB · Views: 125

isladogs

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • db1_CR_v2.mdb
    260 KB · Views: 127
Last edited:

MarkK

bit cruncher
Local time
Today, 01:55
Joined
Mar 17, 2004
Messages
8,178
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

bit cruncher
Local time
Today, 01:55
Joined
Mar 17, 2004
Messages
8,178
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

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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
 

Attachments

  • acces pic 1.gif
    acces pic 1.gif
    61.8 KB · Views: 129

isladogs

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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

bit cruncher
Local time
Today, 01:55
Joined
Mar 17, 2004
Messages
8,178
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

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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)
 

Attachments

  • acces pic 2.gif
    acces pic 2.gif
    69.8 KB · Views: 118

isladogs

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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 :)
 

Attachments

  • acces pic 3.gif
    acces pic 3.gif
    79 KB · Views: 121

MarkK

bit cruncher
Local time
Today, 01:55
Joined
Mar 17, 2004
Messages
8,178
And you unchecked that other missing reference?
Mark
 

KLund1

Registered User.
Local time
Today, 01:55
Joined
Jul 23, 2018
Messages
12
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

MVP / VIP
Local time
Today, 08:55
Joined
Jan 14, 2017
Messages
18,186
If it compiles despite having the MISSING reference, remove that also if not already done
 

Users who are viewing this thread

Top Bottom