Topics Search

Calculate Business Days Through MS Access Events (VBA)

Calculate Business Days Through MS Access Events (VBA)
Views: 91
I need to calculate the number of business days from dates inputted from a form. Does anyone have VBA routine that does this or know how to accomplish this
Sponsored Links:

More topics

 

Calculating an end date from business days

Preview
I have a form (frmTransferInAging) where the user inputs a value for business days (eg 10) into txtBusDays. The value is used by a query to determine 10 business days from today - For example, if 10 was entered today I want to calculate 10 business days before today - how do I calculate this date?

I am currently using the following criteria in the query which returns calendar days:
<=(Date()-([Forms]![frmTransferInAging]![txtBusDays]))

I believe I can create a function to call from the query
 

Net Work Days in Access

Preview
In Excel, I frequently use the formula, Networkdays, to calculate the number of business days that it has taken someone to process an order. I was wondering if there is a way to do the same thing in Access? I have a Project Received Date field and a Project Released Date field. I would like to calculate the business days between these two dates (please note, some of them may cross years (i.e. the project was received 12/28/2011 and released 1/5/2012). Is this possible, or should I stick to doing the calculation in Excel
 

Rucurring Calendar

Preview
I need to build a database that will build a calendar for each account. This calendar will be a recurring calendar. The recurrences could be anywhere from monthly to weekly to every 3rdTuesday to the 4th calendar or the 4th business day.

Now it gets a little trickier. Each Account has 4 monthly events that need the calendar built. The first event date is set by specifying the calendar rule. Each other event is then set based on the other events. For example, we'll call each event by numbers 1 - 4.
Event 1 is set to pay on the 15th of the month. Then the 2nd event pays on Thursday at least 3 business days past Event 1. Event 3 pays 4 business days prior to event 1. And Event 4 pays 15 days prior to event 2.

See what I mean by a little complex?
 

IIf Date Query

Preview
This is in Access 2010 and I am adding days to a date based on a certain day of the week. Sun, Mon, Tue, Wed would add 9 days and that portion works fine. When I add the next part forThu, Fri and Sat it gives me a -1 result for all days.

Sun, Mon, Tue, Wed needs to calculate or add 9 days to Date
Thu, Fri need to calculate or add 11 days to Date
Sat need to calculate or add 10 days to date

This is a database to calculate the expiration day after 7 working days from a specific date.
 

CRITERIA only looks at the LAST 20 BUSINESS DAYS

Preview
I have a query that subtracts two columns. One column is the Date with most recent data, the second column is the date when the data was requested. I want to subtract the date of the request from the date of the most recent data, and if this number is more than 4 BUSINESS DAYS, then to show up in the query.

I can get the query to calculate this, but it includes saturday and sunday in the calculation
 

calculate no. of working days

Preview
First of all Im sorry for my English. Im an a rockie in access and Im like to question the forum about how I can create a date function than calculate the amount of working days between dates (startdate and endate) after take into account the banking holidays that are previously introduced in a table.
 

How to calculate how many days outstanding

Preview
In Access 2007 I have a report that I want to calculate how many days since we received and item and print that number. I selected build event, then Expression Builder, the rest is Date()-[Commercial]![Date Rcvd]=Sum

The only thing that prints for that column is #Days. I'm not well versed in Access, but if anyone has some quick pointers. I want it to be today's date less Date Rcvd column field in our database.
 

Calculating number of days from input.

Preview
How do you calculate the number of days from the original date the the record was recorded.

for example

You have a text box the has three options 90 day, 180 days, and 360 days. And a date and time box to true to show the expiration date.

How do I get the database to show the to calculate the expiration date depending on the number of days that was selected.
 

Access events

Preview
We can trigger VBA code on form events. Can we trigger them on Access application events? For example, can I trigger some vba code when I run a query? Reason I ask is that when I'm running a rather lengthy query, I really cant do much but sit there and wait for it to finish. It makes me look rather unproductive to those who walk by and don't know what's really going on. It doesn't help that Access' progress bar might as well not exist, as it is totally inaccurate. What I want to do is have a from with with a progress bar, more so like the windows start-up bar that shows that scrolls constantly rather than showing how much is done, every time I run a query.

It brings up the larger question of using Access events, rather than form events, to trigger code. Can this be done
 

Function in Query

Preview
I have a query where I have a field NormalDays and SickDays. I want to calculate if someone has Less than 26 days than it will take rest days from SickDays like below

NormalDays = 24
SickDays = 4
I put function like IIF([NormalDays]<26,[NormalDays]+[SickDays],[NormalDays])
But in this case I want query to calculate only 26 days if someone works for 24 days + 3 SickDays my function will say 27 days but I want to give him only 26 days not more than that.
Is there any way out to fix this problem? Please advice