Search results

arenlcp in the query 'qry_saag99_step2, if I put the date #6/01/2020# (Monday), the query result shows all data for that date rather data from 5/29-5/31 (fri-sun)? however, I noticed if I put #5/29/20# in the step2 query it'll combine 5/29-5/31 which is the opposite of what I was looking for...

arnelgp, thanks, i'll check it out today and get back to everyone.

so I tried the function and created a query similar to what you noted arnelgp. in the query, I wanted to see what results would happen if I put Tuesday (5/26/20) to simulate the date as that specific Tuesday but I got results from the same day. so I changed it to simulate the Monday, memorial...

thanks for your assist and help with your expertise arnelgp. i'll let you know. (y)

thank you everyone for chiming in :giggle: if Friday through Monday was a 4 day weekend returning to work on Tuesday, the total production figures would all be under Friday. like arnelgp stated, I did create a holiday table but wasn't certain if this could be written up in sql/query or a...

sorry for not being clear. i'll try an example. so lets say my job does x number of widgets. the data is collected at end of the day. so today, I would be pulling yesterday's total number of widgets. I only want to pull prior weekday. any work done on a weekend would be counted for the Friday...

sorry for the confusion. if today was Monday, I would want to pull the previous weekday, in this case Friday but also include saturday/sunday. however, if the Monday ends up being a holiday like memorial day, if I run my database coming back form work on Tuesday the query or module would look...

did a few searches and majority of the searches were ones excluding weekends and holidays. so im trying to put together a query where I need to pull prior business day (mon-fri). where im trying to figure out is if I can do this in a query/SQL or in a module? basically I need to return prior day...

Sorry, i had thought i deleted this thread. I was able to find a solution to my question in another thread. Ill mark this as solved

oh! it works! I forgot I changed the 9/23 to 9/29 holiday! stupid me!

I copied your code and also changed the where to show #9/24/2019# and somehow still only get 9/23? maybe theres something up with a date format in either the holiday table and/or the table its pulling from

this is what I have: 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...

let me double check

the end goal is to be able to run a task scheduler every morning then have the data linked to excel that runs a refresh macro that populates the data based for the prior workday.

it returns only 9/23/19

so originally with fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference) it returned 9/22 and 9/23 afte changing it to fncIsPrevious1 = (dteFieldValue > dteDate) And (dteFieldValue < dteReference) it returned only 9/23

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

I changed the I - 1 to I-3 and I think it works but need your input: 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...

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.

I get a compile error: statement invalid outside Type Block where this is selected (not highlighted) i As Integer