UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Problem Subtracting Dates Excluding Holidays And Weekends, Any Version    
 
   
Gustav
post Today, 04:47 AM
Post#21


UtterAccess VIP
Posts: 2,245
Joined: 21-February 07
From: Copenhagen


QUOTE
not sure where the code is doing that. it doesn't affect my use, but i'd like to learn - will you help, pls?

Sure. Here for example:

CODE
DCount(1, "tbl00Holidays", "hDate = #" & dat & "#")

dat is not text, thus will be casted to text to be able to concatenate the full string, and that will be done using your local settings. It will work in the US, but just about anywhere else either result in unexpected filtering or simply fail.

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
FrankRuperto
post Today, 08:19 AM
Post#22



Posts: 979
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (Andre)
I don't know if it is relevant, but here in New Zealand we write dates dd/mm/yyyy rather than the way the USA writes them (mm/dd/yyyy). Your code re-formats #yyyy/mm/dd# before processing, so it shouldn't be a problem.

Regardless of how you display dates in any locale, SQL statements will fail if dates are not in English U.S. Format, e.g. #m/d/yyyy#

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
tina t
post Today, 02:19 PM
Post#23



Posts: 6,601
Joined: 11-November 10
From: SoCal, USA


thanks, Gustav, of course the DLookup() criteria argument is a string - duh is me! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
FrankRuperto
post Today, 05:41 PM
Post#24



Posts: 979
Joined: 21-September 14
From: Tampa, Florida USA


Hi Andrew,

How about just using one function for adding or subtracting workdays?

CODE
?CalcWorkDate(#1/7/2020#,-2)   ‘subtract 2 workdays from the input date
1/3/2020
?CalcWorkDate(#1/3/2020#,2)    ‘add 2 workdays to the input date
1/7/2020

The function would use the recordset of a query that filters out all non-working days from the attached lookup table to calculate the workday based on the input date, plus or minus the specified number of days.

I haven't perfected the function yet, my vba still so so, but here's pseudo code:

CODE
Public Function CalcWorkDate(ByVal InDate As Date, ByVal NumDays As Integer) As Date

    Dim str As String, rst As DAO.Recordset, strSQL As String, rec As Boolean
    
    InDate = CDate(Fix(InDate))
    
    strSQL = "SELECT tlkpBizDates.BizDate FROM tlkpBizDates WHERE tlkpBizDates.NoWork  = "FALSE"         ‘query that filters out non-working days from the dates lookup table  
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    rec = Not (rst.BOF And rst.EOF)
    
    If rec = True Then
        rst.MoveLast
        rst.MoveFirst
    End If
    
PSEUDO CODE:  

Select Case

Case when NumDays is a negative number
Rst.FindFirst “InDate – ABS(NumDays)”      ‘subtract the absolute value of NumDays from the input date

Case when NumDays is a positive number
Rst.FindFirst “InDate + ABS(NumDays)”      ‘add the absolute value of NumDays to the input date

END PSEUDO CODE

    rst.Close
    Set rst = Nothing

End Function

This post has been edited by FrankRuperto: Today, 05:43 PM
Attached File(s)
Attached File  DatesLookup.zip ( 78.53K )Number of downloads: 0
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    29th May 2020 - 06:18 PM