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 May 29 2020, 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 May 29 2020, 08:19 AM
Post#22



Posts: 1,014
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 May 29 2020, 02:19 PM
Post#23



Posts: 6,615
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 May 29 2020, 05:41 PM
Post#24



Posts: 1,014
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.

EDIT: Okay, here's a better mouse trap, fewer moving parts smile.gif This vba sub runs when you press the "GetDate" form control in the attached db.

CODE
Option Compare Database

Private Sub cmdGetDate_Click()

    Dim rs As Recordset
    Dim dtSelectedDate As Date
    Dim strSQL As String
    Dim intIncrement As Integer
    
    dtSelectedDate = Me.SelectDate
    
    
    If Me.Increment > 0 Then
        strSQL = "SELECT tlkpBizDates.BizDate, tlkpBizDates.NoWork FROM tlkpBizDates WHERE ((tlkpBizDates.BizDate) >= # " & dtSelectedDate & "#) And " _
                  & "((tlkpBizDates.NoWork) = False) ORDER BY tlkpBizDates.BizDate ASC"
    Else
        strSQL = "SELECT tlkpBizDates.BizDate, tlkpBizDates.NoWork FROM tlkpBizDates WHERE ((tlkpBizDates.BizDate) <= # " & dtSelectedDate & "#) And " _
                  & "((tlkpBizDates.NoWork) = False) ORDER BY tlkpBizDates.BizDate DESC"
    End If
    
    
    Set rs = CurrentDb.OpenRecordset(strSQL)

    rs.MoveFirst
        For intIncrement = 1 To Abs(Me.Increment)
            rs.MoveNext
        Next
        Me.NewDate = rs!bizdate
        
    Set rs = Nothing
    
End Sub

This post has been edited by FrankRuperto: May 29 2020, 06:25 PM
Attached File(s)
Attached File  DatesLookupVer1.1.zip ( 72.25K )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
 
FrankRuperto
post May 29 2020, 09:02 PM
Post#25



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


I forgot to include the form examples:

Attached File(s)
Attached File  CalcDay.PNG ( 138.89K )Number of downloads: 8
 

--------------------
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    5th June 2020 - 11:13 AM