Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2019, 03:33 AM   #1
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Week Number from Date for Fiscal Year

Hi Everyone - newbie poster

Could you support with the following please-

I have a list of dates dd:mm:yyyy for transactions which I need to allocate a week number to.

Criteria is the Fiscal year starts on the 1st September each year.

Thus 1/9 is always Week 1

Many thanks
Garmani

Garmani is offline   Reply With Quote
Old 09-23-2019, 03:41 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,315
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Week Number from Date for Fiscal Year

Find the date difference in days and divide by 7?
Add 1 to adjust perhaps?

https://docs.microsoft.com/en-us/off...ediff-function
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Garmani (10-04-2019)
Old 09-23-2019, 04:02 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,901
Thanks: 114
Thanked 2,981 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Week Number from Date for Fiscal Year

WeekNumber=DateDiff("w",#9/1/2019#,me.datefield)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Garmani (10-04-2019)
Old 09-23-2019, 04:14 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Week Number from Date for Fiscal Year

create yet another function:
Code:
Option Compare Database
Option Explicit

Public Function fnkFiscalWeek(dte As Variant) As Integer
    
    Dim dteFiscal As Date
    
    If IsEmpty(dte) Then Exit Function
    dteFiscal = Dateserial(Year(Date), 9, 1)
    If dte < dteFiscal Then
        dteFiscal = DateAdd("yyyy", -1, dteFiscal)
    Else
        If DateDiff("m", dteFiscal, dte) > 12 Then
            dteFiscal = DateAdd("yyyy", 1, dteFiscal)
        End If
    End If
    'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
    fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function
NOT tested.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 05:05 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,215
Thanks: 90
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Week Number from Date for Fiscal Year

Garmani,

Quote:
I have a list of dates dd:mm:yyyy for transactions which I need to allocate a week number to.
Are these transactions all in the current fiscal year? If not, how do you handle week number in previous or future fiscal years?

Did you design the date formatting with ":" separator? If so, why?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 09-23-2019 at 05:27 AM.
jdraw is online now   Reply With Quote
Old 09-23-2019, 07:54 AM   #6
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Re: Week Number from Date for Fiscal Year

Thank you for your reply - you may have to guide me with this one as I am struggling to call the function
Thanks
Garmani
Garmani is offline   Reply With Quote
Old 09-23-2019, 07:57 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Week Number from Date for Fiscal Year

is it my function?

you can call it in a query:

select [transID], [transDate], fnkFiscalWeek([transDate]) As Week# From yourTransTable;

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 08:10 AM   #8
Garmani
Newly Registered User
 
Join Date: Sep 2019
Posts: 10
Thanks: 6
Thanked 0 Times in 0 Posts
Garmani is on a distinguished road
Re: Week Number from Date for Fiscal Year

Yes its your function -
As per previous attachment - the Deliv#Date field contains the data I want to return a week number. For the Fiscal year commencing 01/09/2018 to 31/08/2019.

Apologies I am just trying to be aligned with your solution

Regards
Garmani
Attached Files
File Type: xlsx q_DHL_SL_Availability.xlsx (208.5 KB, 6 views)
Garmani is offline   Reply With Quote
Old 09-23-2019, 08:53 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Week Number from Date for Fiscal Year

I made changes to the function:
Code:
Public Function fnkFiscalWeek(dte As Variant) As Integer
    
    Dim dteFiscal As Date
    
    If IsEmpty(dte) Then Exit Function
    dteFiscal = DateSerial(Year(Date), 9, 1)
    If dte < dteFiscal Then
        While dte < dteFiscal
            dteFiscal = DateSerial(Year(dteFiscal) - 1, 9, 1)
        Wend
    Else
        If DateDiff("m", dteFiscal, dte) > 12 Then
            While DateDiff("m", dteFiscal, dte) > 12
                dteFiscal = DateSerial(Year(dteFiscal) + 1, 9, 1)
            Wend
        End If
    End If
    'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
    fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function
Attached Files
File Type: zip q_DHL_SL_Availability (1).zip (203.6 KB, 6 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Garmani (10-04-2019)
Old 09-23-2019, 11:23 AM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Week Number from Date for Fiscal Year

I do not believe the function works for dates in September in out years.
?fnkFiscalWeek(#9/2/2020#)
53
Believe that should be 1
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Garmani (10-04-2019)
Old 09-23-2019, 11:49 AM   #11
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,315
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Week Number from Date for Fiscal Year

Would

Code:
dteFiscal = DateSerial(Year(dte), 9, 1)
correct it. though it does give 53 for #08/31/2020#

Quote:
Originally Posted by MajP View Post
I do not believe the function works for dates in September in out years.
?fnkFiscalWeek(#9/2/2020#)
53
Believe that should be 1
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Garmani (10-04-2019)
Old 09-23-2019, 12:28 PM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Week Number from Date for Fiscal Year

correction made. it's an aircode btw.
Code:
Public Function fnkFiscalWeek(dte As Variant) As Integer
    
    Dim dteFiscal As Date
    
    If IsEmpty(dte) Then Exit Function
    dteFiscal = DateSerial(Year(DATE), 9, 1)
    If dte < dteFiscal Then
        While dte < dteFiscal
            dteFiscal = DateSerial(Year(dteFiscal) - 1, 9, 1)
        Wend
    Else
        If DateDiff("d", dteFiscal, dte) > 365 Then
            While DateDiff("d", dteFiscal, dte) > 365
                dteFiscal = DateSerial(Year(dteFiscal) + 1, 9, 1)
            Wend
        End If
    End If
    'fnkFiscalWeek = ((DateDiff("d", dteFiscal, dte)) \ 7) + 1
    fnkFiscalWeek = DateDiff("w", dteFiscal, dte) + 1
End Function
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2019, 01:32 PM   #13
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,384
Thanks: 11
Thanked 2,280 Times in 2,232 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Week Number from Date for Fiscal Year

Code:
correct it. though it does give 53 for #08/31/2020#
Every year spans 53 weeks. Some span 54.
plog is online now   Reply With Quote
Old 09-23-2019, 03:31 PM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,829
Thanks: 35
Thanked 556 Times in 527 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Week Number from Date for Fiscal Year

Quote:
Every year spans 53 weeks. Some span 54
Just like a month can span from 4 to 6 weeks.

Six weeks
Attached Images
File Type: jpg 6weeks.jpg (31.3 KB, 25 views)

MajP is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of Pay Periods in a Fiscal Year homage Queries 3 09-10-2014 09:00 AM
Query has week number and year from a date field. Solo7 Queries 1 07-23-2008 10:32 AM
Get report to total week, month, quarter & fiscal year To Date TammyIS Reports 0 03-21-2007 12:00 PM
HELP: Changing dates to FY (fiscal year) and YTD (year-to-date) values MehranT General 7 04-26-2006 04:43 AM
number fields in text to number field, formula for fiscal year shurleexyz Tables 6 01-03-2005 09:15 AM




All times are GMT -8. The time now is 03:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World