Add a specified number of days to a date, passing weekends and holidays

[newbiecoder]

How would I place this into a query? I apologize, I am fairly new at this. I tried creating a query with two columns. 1. TICKET_NO (from tblDaysRemainingCalculation) 2. the dlookup you provided - SLA_DUE_DATE: DLookUp("[WORK_DATE]","[tblBusinessDayTracker]","[WORK_DATE]=#" & "Date"+DLookUp("[DAYS_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICKET_NO] & "'") & "#")

[Gicu]

SELECT [TICKET_NO],[DAYS_REMAINING], dlookup("[WORK_DATE]","[tblBusnessDaysTracker]","[WORK_DATE]=#" & Date +dlookup("[DAYS_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICHET_NO] &"'") & "#") AS DUE_DATE FROM tblDaysRemainingCalculation;

[newbiecoder]

for some reason its not calculating for all the records. It's only calculating when days remaining is a whole number and does not include a decimal. i.e. 5.000 vs 4.531

[Gicu]

use FiX function to get the days part:

https://support.office.com/en-us/art...8-7e3153984ff8

Vlad

[newbiecoder]

Now they are all throwing an error.

SELECT [TICKET_NO],[DAYS_REMAINING], dlookup("[WORK_DATE]","[tblBusnessDaysTracker]","[WORK_DATE]=#" & Date +dlookup("[WHOLE_DAYS_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICkET_NO] &"'") & "#") AS DUE_DATE FROM tblDaysRemainingCalculation;

column Whole_days_remaining is built into the query and contains the FIX for Days_remaining

[newbiecoder]

here is the sql I am using

SELECT tblDaysRemainingCalculation.TICKET_NO, Fix([DAYS_REMAINING]) AS WHOLE_DAYS_REMAINING, DLookUp("[WORK_DATE]","[tblBusinessDayTracker]","[WORK_DATE]=#" & Date()+DLookUp("[WHOLE_DAYS_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICKET_NO] & "'") & "#") AS DUE_DATE
FROM tblDaysRemainingCalculation;

[Gicu]

Have you tried:

SELECT tblDaysRemainingCalculation.TICKET_NO, DLookUp("[WORK_DATE]","[tblBusinessDayTracker]","[WORK_DATE]=#" & Date()+FIX(DLookUp("[DAYS_REMAINING]","[tblDaysRemainingCalculation]","[TICKET_NO] ='" & [TICKET_NO] & "'")) & "#") AS DUE_DATE
FROM tblDaysRemainingCalculation;

Vlad

[newbiecoder]

Okay I think its almost there. The only problem is that if the day falls on the weekend it just comes in blank, whereas I need it to show the next business day

[Gicu]

I see. I think you need to build a top one query first to get the first business date (basically get the first date >= than the value returned by the dlookup). Can you upload a small Access file with your two tables (just leave a couple sample records in the main one) and I might be able to help you if you get stuck.

Cheers,
Vlad

[newbiecoder]

pw: StmtCorrect

[Gicu]

Check the updated query and see if it looks OK.

Cheers,
Vlad

[newbiecoder]

This is amazing! Thank you so much! Question though, if I wanted to incorporate time I would just add the decimal value to the date right? Also, lets say that its Friday around 8PM..if I add .5 to the correct date it would end up changing to Saturday as the due date

[Gicu]

Adding time will add a whole new level of complexity; are you looking to incorporate "regular business hours"? Also, I assume you run a make table or update query to update the days_remaining field, why not use the calculated field in my query so it would give you correct results every time.

[newbiecoder]

Adding time will add a whole new level of complexity; are you looking to incorporate "regular business hours"? Also, I assume you run a make table or update query to update the days_remaining field, why not use the calculated field in my query so it would give you correct results every time.

I’m not looking to only stick within working hours, because the teams processing hours vary. I can attach what I am using to calculate the days remaining; that may help.

[Gicu]

Sure, do that and I'll have a look.

Cheers,
Vlad