Results 1 to 11 of 11
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    Customized calculation based on written date in Access

    Dear friends,

    I am struggling for two points in MS Access.

    I have written the formula in MS Excel so that you can understand very easily what I am trying to do in MS Access with query.

    The first question:
    I would like to get the week of the year considering the given date from user. But this will not work directly in the same way Weeknum formula works in Excel. This will be customized formula. I wrote this formula in Excel already (D49 is the cell). What I want is directly to reach the same result in access converting the formula as query.

    If(Or(D49=Date(2021;1;1);D49=Date(2021;1;2);D49 = Date(2021;1;3));"Week53";If(Year(D49)=2021;Weeknum (D49;2)-1;weeknum(D49;2)))





    The second question
    it is about adding workdays exluding saturday and sunday to the written date field. How can I write it with query?

    For example;
    Lets say Date3 is one of the field of the table5 as a date data type. So user will enter the date into Date3 field manually in the form.
    In that form Date4 field will be the other data which is calculated by the query considering the Date3 value.

    So if I make it short;
    Table name: table5
    Field name of table5: date3
    date4 = date3 + 10 workdays. if user enter 17.12.2020 for date3, then date4 should be calculated as 31.12.2020

    Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in access, text box or query fld:
    =dateadd("d",10,[date3])

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Q1. Looks like a solution for just this year- is that what you really want? Also one result is text the other is numeric. Ok for excel but not access.

    q2. Define working days. Is that Monday to Friday? Do you exclude bank holidays? Other closures?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    You want to get ISO weeknumber? I.e. the 1st week of year is the one with 1st Thursday of this year in it, a week starts with Monday, and it is always 7 days long.

    One way is to go like explained here https://stackoverflow.com/questions/...rd-week-number

    Then you can try to port this Excel formula into Access:
    Code:
    =1+INT((SomeDate-DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;5)+WEEKDAY(DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;3)))/7)
    , or the same formula ported into MS SQL Server as an UDF (I havent ported it into Access, as for years now I use Access only as FrontEnd app):
    Code:
    USE [utilities]
    GO
    /****** Object:  UserDefinedFunction [dbo].[udf_IsoYYYYWW]    Script Date: 18.12.2020 9:36:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Arvi Laanemets
    -- Create date: 07.06.2012
    -- Description:    Returns the ISO week of a date in format yyyyww
    -- =============================================
    ALTER FUNCTION [dbo].[udf_IsoYYYYWW] 
    (
        -- Add the parameters for the function here
        @Date datetime
    )
    RETURNS int
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @IsoWeek int
    
    
        -- Add the T-SQL statements to compute the return value here
    
    
        SET @ISOWeek = 
            (
                1 + 
                Datediff(
                    day,
                    0,
                        @Date - 
                        utilities.dbo.udf_Date(
                            Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
                            1,
                            5
                        ) + 
                        dbo.udf_WeekWd(
                            utilities.dbo.udf_Date(
                                Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
                                1,
                                3
                            ),
                            7
                        )
                )/7
            )
        SET @ISOWeek = 
            (
                YEAR(@Date) + 
                (CASE WHEN Day(@Date)<4 AND @ISOWeek>=52 THEN -1 WHEN Day(@Date)>=28 AND @ISOWeek=1 THEN 1 ELSE 0 END)
            )*100 + 
            @ISOWeek
    
    
        -- Return the result of the function
        RETURN @IsoWeek
    
    
    END

  5. #5
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by ranman256 View Post
    in access, text box or query fld:
    =dateadd("d",10,[date3])
    Hello Ranman256,

    Thanks for answer, But this function gives us a result just adding direct numbers to the date field. But as I explained it should be skip saturday and sunday.

  6. #6
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by Ajax View Post
    Q1. Looks like a solution for just this year- is that what you really want? Also one result is text the other is numeric. Ok for excel but not access.

    q2. Define working days. Is that Monday to Friday? Do you exclude bank holidays? Other closures?
    Hello Ajax, Thanks for your answer.
    I solved the first question. This formula works in a way what I want. "Week-" & DatePart("ww";[DateOfEntry]) & "/" & Year([DateOfEntry])

    Second question --> working days will be from monday to friday. Saturday and Sunday need to be skipped. I dont have another holiday considerations.

  7. #7
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by ArviLaanemets View Post
    You want to get ISO weeknumber? I.e. the 1st week of year is the one with 1st Thursday of this year in it, a week starts with Monday, and it is always 7 days long.

    One way is to go like explained here https://stackoverflow.com/questions/...rd-week-number

    Then you can try to port this Excel formula into Access:
    Code:
    =1+INT((SomeDate-DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;5)+WEEKDAY(DATE(YEAR(SomeDate+4-WEEKDAY(SomeDate+6));1;3)))/7)
    , or the same formula ported into MS SQL Server as an UDF (I havent ported it into Access, as for years now I use Access only as FrontEnd app):
    Code:
    USE [utilities]
    GO
    /****** Object:  UserDefinedFunction [dbo].[udf_IsoYYYYWW]    Script Date: 18.12.2020 9:36:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Arvi Laanemets
    -- Create date: 07.06.2012
    -- Description:    Returns the ISO week of a date in format yyyyww
    -- =============================================
    ALTER FUNCTION [dbo].[udf_IsoYYYYWW] 
    (
        -- Add the parameters for the function here
        @Date datetime
    )
    RETURNS int
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @IsoWeek int
    
    
        -- Add the T-SQL statements to compute the return value here
    
    
        SET @ISOWeek = 
            (
                1 + 
                Datediff(
                    day,
                    0,
                        @Date - 
                        utilities.dbo.udf_Date(
                            Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
                            1,
                            5
                        ) + 
                        dbo.udf_WeekWd(
                            utilities.dbo.udf_Date(
                                Year(@Date+4-utilities.dbo.udf_WeekWd(@Date+6,7)),
                                1,
                                3
                            ),
                            7
                        )
                )/7
            )
        SET @ISOWeek = 
            (
                YEAR(@Date) + 
                (CASE WHEN Day(@Date)<4 AND @ISOWeek>=52 THEN -1 WHEN Day(@Date)>=28 AND @ISOWeek=1 THEN 1 ELSE 0 END)
            )*100 + 
            @ISOWeek
    
    
        -- Return the result of the function
        RETURN @IsoWeek
    
    
    END

    Hello ArviLaanemets,
    Thank you very much. I have solved the first question.

    You have said you are using the MS Access as frond end while the SQL server is back end.
    I am now so close to finish my Access database (2016) which include both Front end and back end. This will be used by around 200 users. I have 13 table and 13 forms. Current size of the access is 30 MB. I think it will be 1 GB after a year. So I have a plan to use SQL Server as back end.
    I am not sure at some points. For example if I use SQL Server,

    - Will the general performance (speed for operations) for users be increased or not?
    - Can I not use the MS Access for 200 users choosind edited option?
    - Will my VBA code be working further when I transfer the back end to SQL Server?

    And the last thing, as it will be my first experience to use SQL Server together with Access, Do you have any information that you can recommend me how should I transfer my backend from access into SQL server correctly?

    Thanks for your time.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are lots of routines to calc working days. Take a look at the attached dB

    And welcome to the forum...
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 7 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    About calculating various info about working days - I prefer to have a calendary table and a procedure which fills it to certain date in future. The table has date as PK, and can have any number additional fields you may need, like day type (values for workday, weekend, holiday, short workday before holiday, etc.), normative work hours, week number, etc. The procedure is run manually, from button on your form, or from Open event of your form (with this option, you have to check e.g. the calendary table having max date less than designed for current date), only adds new rows, and as it is run once for some longer period, calculating all fields doesn't affect everyday peromance of your database. When using SQL Server as BE, you can have this table in special utility database like the UDF in my previous post, and you have a work which updates the table e.g. once in year.

    With such calendary table, you can calculate whatever you want easily, running regular aggregate functions, or linking this table into your queries.

    Having tis table in special database on SQL server allows you to use this table from all your apps.

  11. #11
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by ssanfu View Post
    There are lots of routines to calc working days. Take a look at the attached dB

    And welcome to the forum...
    Thank you for the sharing. its what I look for.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dmax calculation, based on Date criteria
    By jainkamleshd in forum Access
    Replies: 7
    Last Post: 04-04-2019, 06:42 AM
  2. Replies: 2
    Last Post: 10-30-2014, 09:40 AM
  3. Date month calculation based on combo box
    By wnicole in forum Access
    Replies: 4
    Last Post: 10-27-2013, 08:04 PM
  4. Field content based on date calculation
    By jlmnjem in forum Programming
    Replies: 6
    Last Post: 09-23-2010, 10:24 AM
  5. Replies: 1
    Last Post: 01-30-2010, 04:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums