Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-18-2019, 11:08 AM   #16
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

do you have previous data, why wait till Monday when you can adjust the computer date to previous Monday and test it.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-18-2019, 11:14 AM   #17
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

^true but that requires a manual input which the idea was to automate as much as I can without interruption. im still a noob, but arnelgp, can you elaborate on some of your line coding please?

what's going on here? is it just saying if it errors to not show a message?
Code:
fncIsPrevious = False
I presume dl is the date in the field and if empty just resume/quit?
Code:
If IsEmpty(d1) Or IsNull(d1) Then Exit Function
fst is offline   Reply With Quote
Old 09-18-2019, 11:15 AM   #18
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

Quote:
Originally Posted by arnelgp View Post
do you have previous data, why wait till Monday when you can adjust the computer date to previous Monday and test it.
that is true, let me try that method.

fst is offline   Reply With Quote
Old 09-18-2019, 11:30 AM   #19
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

first statement set the function to return false.
second line test your field date if null, if it is, and since we already set the function to return false, the function will exit and will return the False.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
fst (09-19-2019)
Old 09-19-2019, 04:50 AM   #20
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

thanks for the clarification arnelgp.
fst is offline   Reply With Quote
Old 09-19-2019, 05:14 AM   #21
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

one more question, if Monday's a holiday how would the coding be to add that into Friday's totals? I currently have a table called tbl_Holidays where field one is called "Holiday" and the other field is called "Date". I was trying to see if the coding arnelgp first posted for excluding holidays to include it in the If statement but I get an error due to the DCount line.
fst is offline   Reply With Quote
Old 09-19-2019, 05:26 AM   #22
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

that is for you to tell.
what do you do on Monday holiday?
if you come Monday(holiday) and print, same as usual (print-out: fri-sunday).
tues printout (Monday date, regardless if it is holiday?).

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-19-2019, 05:39 AM   #23
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

sorry for not being clear. so, if Monday's a holiday, and I come in Monday then print-out is fri-sun (the query looks in the tbl_holiday if any holiday exists).
tues printout would be is checking the tbl_Holiday and if exists, adds fri-mon

I suppose another example would be is thanksgiving and black Friday. come Monday when I run the query, it would look in the tbl_holiday and see 11/28/19 and 11/29/19 and count wed-sun since wed was the last known work day.
hopefully that makes a bit more sense
fst is offline   Reply With Quote
Old 09-19-2019, 06:05 AM   #24
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

test this:
Code:
Public Function fncIsPrevious(dteFieldValue As Variant, ByVal dteReference As Date) As Boolean
    i As Integer
    Dim dteDate As Date
    
    fncIsPrevious = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    i = -1
    
    Do While True
        dteDate = DateAdd("d", i, dteReference)
        If DCount("1", "tbl_holiday", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, "mmm")) = 0 Then
                Exit Do
            End If
        End If
        i = i - 1
    Loop
    fncIsPrevious = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-19-2019, 06:13 AM   #25
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

I get a compile error: statement invalid outside Type Block where this is selected (not highlighted)
Code:
i As Integer
fst is offline   Reply With Quote
Old 09-19-2019, 06:15 AM   #26
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

sorry, replace it with:

Dim i As Integer
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-19-2019, 06:34 AM   #27
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

if I add +5 days from todays date (9/24/19) and put in my tbl_holiday that 9/23/19 is a holiday, it is skipping Friday.
fst is offline   Reply With Quote
Old 09-19-2019, 06:43 AM   #28
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

I changed the I - 1 to I-3 and I think it works but need your input:
Quote:
i = i - 3
Code:
Public Function fncIsPrevious1(dteFieldValue As Variant, ByVal dteReference As Date) As Boolean
    Dim i As Integer
    Dim dteDate As Date
    
    fncIsPrevious1 = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    i = -1
    
    Do While True
        dteDate = DateAdd("d", i, dteReference)
        If DCount("1", "tbl_holidays", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, "mmm")) = 0 Then
                Exit Do
            End If
        End If
        i = i - 3
    Loop
    fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function
fst is offline   Reply With Quote
Old 09-19-2019, 06:47 AM   #29
fst
Newly Registered User
 
Join Date: Apr 2018
Posts: 38
Thanks: 9
Thanked 0 Times in 0 Posts
fst is on a distinguished road
Re: count weekdays into friday

ok nevermind, it didnt quite work. if Friday and Monday was a holiday, ie a 4 day weekend (I put 9/20 and 9/23 as a holiday), changing the 1 to a 3 also adds in Wednesday but should only be adding thurs-mon
fst is offline   Reply With Quote
Old 09-19-2019, 07:03 AM   #30
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: count weekdays into friday

then change this:

fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)

to:

fncIsPrevious1 = (dteFieldValue > dteDate) And (dteFieldValue < dteReference)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count number of distinct weekdays in Group By troubledesire Queries 4 05-01-2010 04:17 AM
Count Weekdays Between 2 Dates eckert1961 Modules & VBA 5 03-04-2009 01:08 PM
Next Friday Groundrush General 19 11-28-2008 09:47 AM
Weekdays in Middleeast... darno Forms 0 07-04-2006 04:48 AM
Weekdays... bodylojohn General 4 01-04-2006 04:49 AM




All times are GMT -8. The time now is 05:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World