I currently have a table of all the business days of the year. WorkingDays [BusDate]. I have a file that contains the current date WorkingFile [CurrentDate]. I am trying to figure out how to find the previous business day from the [BusDate] field in the WorkingDays table using the [CurrentDate] in the WorkingFile table, and return it to a field labeled [PriorBusDate] in the WorkingFile. For some reason this is difficult for me.
There are multiple ways of doing this, but I would use a conditional statement to figure out the day of the week and base my calculation off of that.
if DateTimeFormat(DateTimeAdd([Current Date],-1,'days'),'%a') = 'Sun' then DateTimeAdd([Current Date],-3,'days')
elseif DateTimeFormat(DateTimeAdd([Current Date],-1,'days'),'%a') = 'Sat' then DateTimeAdd([Current Date],-2,'days')
else
DateTimeAdd(DateTimeNow(),-1,'days') endif