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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Add Working Days To A Query, Any Version    
 
   
jfearn
post Oct 16 2019, 09:29 AM
Post#1



Posts: 1
Joined: 16-October 19



Hi

Im hoping someone out there can help me to add a number of working days to an existing date. Excluding weekends. Eg, add 3 working days to a date. In my current query, I have =[Date]+5, but of course this does not exclude working days. Is there something I can put into this query to exclude these?

Ive looked at other responses and all have the answer of creating VBA to resolve this. Im afraid I dont know any VBA at all, just know how to create queries.
If this is the only solution, how do I do this; how do I put this into my query? What do I type; how do I link this into my query

I have MS Access on Office 365

Any help appreciated

Thanks in advance!

Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 09:33 AM
Post#2


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

To do this correctly, yes, VBA is required. Time to expand your toolbox.

We have code samples here in our archives that handle this kind of task.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 09:36 AM
Post#3


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


THis is probably my favorite.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 16 2019, 09:51 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you can create a function in VBA:
CODE
Public Function fncAddWorkDays(start_date As Variant, days_to_add As Integer) As Variant
    Dim dte As Date
    
    fncAddWorkDays = Null
    If IsNull(start_date) Then Exit Function
    dte = start_date
    Do While days_to_add > 0
        dte = DateAdd("d", 1, dte)
        If Instr(1, "Sat/Sun", Format(dte,"ddd")) <> 0 Then
            days_to_add = days_to_add - 1
        End If
    Loop
    fncAddWorkDays = dte
End Function

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 10:03 AM
Post#5


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


Use the Add Working Day Function in a query like this:

SQL
SELECT tblExercise.ExerciseID
, tblExercise.ExerciseDate
, fAddWorkdays([ExerciseDate],2) AS NextScheduledExercise
FROM tblExercise;

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 03:20 AM