Results 1 to 2 of 2
  1. #1
    ac1981 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    1

    Translate SQL to Access Query Built Formula

    Hi I am trying to translate this SQL to a Built formula in Access.


    can you help me?

    CREATE FUNCTION: fnEarnedDays
    ---
    CREATE FUNCTION [dbo].[fnEarnedDays]
    (
    @TranEffDt Date,
    @TranTypeCd varchar(25),
    @TermExpDt Date,
    @EarningDt Date, --EOM
    @BookedDt Date = null,
    @CreateDt Date = null
    )
    RETURNS int
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    DECLARE @inForce int = 0
    DECLARE @FOM DATE = DATEADD(mm, DATEDIFF(mm, 0, @EarningDt), 0)

    SELECT @inForce =
    CASE
    When not @CreateDt is null and @CreateDt >= @TermExpDt
    and datepart(month, @EarningDt) = datepart(month, @TermExpDt)
    and datepart(year, @EarningDt) = datepart(year, @TermExpDt)
    Then CONVERT(real, datediff(day, @TranEffDt, @TermExpDt))
    When not @CreateDt is null and @CreateDt >= @TermExpDt
    Then 0 --CONVERT(real, datediff(day, @TranEffDt, @TermExpDt))
    When not @BookedDt is null and @EarningDt < @BookedDt
    Then 0
    --When @TranTypeCd = 'CN' and @TranEffDt > @FOM and @TranEffDt < @EarningDt
    Then CONVERT(real, datediff(day, @FOM, dateadd(day, -1, @TranEffDt))) --(Removed this because of 2691969)
    When not @BookedDt is null and @EarningDt = @BookedDt
    Then CONVERT(real, datediff(day, @TranEffDt, dateadd(day, 1, @EarningDt)))
    When @TranEffDt >= @FOM
    Then CONVERT(real, datediff(day, @TranEffDt, dateadd(day, 1, @EarningDt)))
    When @TermExpDt <= @EarningDt
    Then CONVERT(real, datediff(day, @FOM, @TermExpDt))
    else
    CONVERT(real, datediff(day, @FOM, DATEADD(day, 1, @EarningDt)))
    END

    RETURN(@inForce)
    END




    GO

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I expect will need to create a VBA function.

    Provide sample data and describe desired output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Built-In Formula in Access Db not working
    By rdyson39 in forum Access
    Replies: 5
    Last Post: 01-23-2019, 02:44 PM
  2. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  3. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  4. Anyone able to translate a formula from excel?
    By dniezby in forum Programming
    Replies: 10
    Last Post: 05-21-2013, 01:10 PM
  5. Replies: 0
    Last Post: 09-03-2009, 01:58 PM

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