Calculating Workdays... And Previous Threads

Nov 14, 2006

Hello.

I've been trying very hard to get through the various instructions, code, modules, etc, on calculating workdays. I am having no luck in getting this done. As one who can write an Access database only to the point of a little VBA, using mostly queries and formulae to get by, I have difficulty with posted code from time to time.

For example, in a previous thread (actually, in several previous threads), code was posted with the instruction (essentially), "Here. Use this." To which someone replies, "Thanks, worked a charm." Or the like. As one who struggles and has limited responsibilities in this area (and little time to learn while on the job), posted code can be a blessing... but "Here. Use this." can be its attached curse. "HOW do I use it?" is the next question. Is it a module? Do I put it as code on the On Open event for a form? Is it an expression I build into a query? Sometimes, seeing the code is only the first step... and for one like me, can lead to a lot of trouble.

Also, many have been kind enough to post databases, which show the end result of much work. This is also greatly appreciated, believe me. But, again, once I start looking to HOW it was built... the investigation sometimes leads nowhere. Where do I look for code? Did I miss something? Why doesn't mine work like that?

So, for the question I have now, I have searched this and other forums. I have copied and pasted code that I thought should work. I have copied code into a module and tried to call up that module in a query. I have followed instructions as well as I could... but you can guess where this is headed. What I have is, in the simplest form, a table called Employees with the fields Name, StartDate and EndDate. I need to know how many workdays there are in the date range of StartDate to EndDate, including the dates in those fields.

If you have answered this question before, I apologize for not being able to figure this out. Any help is greatly appreciated.

View Replies


ADVERTISEMENT

Queries :: Calculating Percentage Change From Previous Records

Oct 1, 2014

I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).

I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.

I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.

View 8 Replies View Related

How Do I Search On Just My Threads?

Sep 5, 2006

Silly I know and it is more than likely on the site but I can't find it. I want to search on just my threads to check to replies etc.
Tee.

View 5 Replies View Related

This Question Actually Goes In Two Threads.....

Jul 21, 2007

I am trying to build a db for my small company. I would like to set up a report that has the same functions as a bank statement so I can show my clients what they have made on a certain date and what they have spent, as well as the balance. I thought it'd be a simple SQL but was wrong. I went to several other db forums and was led only as far as I got on my own then they seemed to have left. Is it too difficult?

I need to take the [Total] of a sale, multiply it by .3 (this is our commission). What's left in the [Total] I need to subtract the various expenses (PostageCost, CopiesCost, GasCost, OtherCost, PhoneCost) to have a balance. Again, the entire report should look like a bank statement with either a debit or credit on certain dates. Each report is printed monthly for each individual clients.

Does that make sense?

View 13 Replies View Related

Calculating Field Based On Value Of Another Field In Previous Record

Sep 18, 2014

I'm trying to create something like an online banking view that shows the running balance as each transaction occurs. I have tblTransactions with fields AccountNumber, ItemDescription, and TransactionAmount. I'm trying to create something that shows these three fields and a fourth field with the running balance.

So if I initially deposit $100 it will show the first record with TransactionAmount = $100 and RunningBalance = $100. Then the next transaction will subtract the Transactionamount for the new record from the RunningBalance from the previous record to get the RunningBalance for the new record. So if I make a purchase for $2, the AvailableBalance for that record is $98. Is this possible with a query? Here's a picture to describe what I'm talking about ....

View 3 Replies View Related

Calculation Workdays Between 3 Dates

Oct 10, 2006

I have a table with 3 columns

Order Date (f.e. 05/10/2006)
Due Date (f.e. 09/10/2006)
Ship Date (f.e. 10/10/2006)

I would like to calculate:

number of working days between order date and due date
number of working days between due date and ship date

Can someone help me with this one?:confused:

View 6 Replies View Related

Modules & VBA :: Calculate Deadline With Workdays And Holidays

Mar 19, 2014

I am trying to get a module set-up that will add a number of workdays (no weekends, no holidays) to a date that is mentioned in a form and put the resulting deadline date in another field on the same form (date or remaining days to deadline would be even better).

Now it gets a little tricky, the module will need to select different amounts of workdays to be added to the date in the form depending on what status is selected (different statusses have different amounts of days).

I'll probably need:
List of holidays
List of days required per status
Start date (to which the days can be added).

Example:

Status = "DQ" which has 2 days to work with.
Start date in form = "21/03/2014".
Result = 25/03/2014 or preferably "2 days remaining".

View 14 Replies View Related

Queries :: Counting Days Between 2 Dates - Just Workdays

Oct 11, 2013

I have 2 dates that I need to count between. Easy enough just use the datediff right? Nope cause it won't count just the 5 workdays. I researched and found that the "w" in the function doesn't work the way I need it to. I found lots of code to make a module that will do it for me and they all include having a holiday table. Right now I don't need a holiday table I just want the simple dates in between.

View 2 Replies View Related

Queries :: How To Calculate Number Of Workdays In Access 2013

Aug 11, 2014

In the attached image I was trying to calculate number of working days(throughput) b/w item received_date and item_released date by using datadiff with "w" interval . but if I use "w" interval it brings value zero .but if I use "d" interval then it brings result with inclusive of Saturdays and Sundays.i don't need that.

How to calculate the number of working days exclusive of Saturdays and sundays.

View 1 Replies View Related

Previous Value Plus 1

Jul 27, 2005

I want to add a default value to field in a record, and it needs to be the value of the previous record plus one.

Ive searched the site and tried access help but to no avail.

Any help would be gratefully received

Thanks.

View 1 Replies View Related

Calculating

Feb 19, 2007

I have a table with 2 coloums with nuberin them. I would like to add those 2 number together and display them in a 3rd coloum. I need to automatically change the 3rd coloums value should any others change. Is this possible?

View 4 Replies View Related

Calculating Age.

Jan 19, 2007

age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))Dob is stored yyyyddmmI thought this above function looks ok. When I run it, I get a data type mismatch in criteria expression.What am I missing?Any help would be appreciated.Thanks

View 6 Replies View Related

Calculating In VBA

Sep 20, 2005

hi all,

i have a mainform with a subform.

in the subform i have 2 fields: duur and subtotaal
in duur i enter a number.
in subtotaal a calculation has to occur: =20*([duur]/60)
i tried to do this calculation in VBA but then i got the same value on every record in the subform.

But now the problem:
in the mainform i have a control 'totaal' that needs to do a DSum of subtotaal.
And here i am getting into troubles since subtotaal is not stored in the table but calculated.

The formulla for DSum would be:
Me.Parent!totaal = DSum("subtotaal", "tblFacturenDetail", "[FactuurID] = " & Me.Parent!FactuurID)

but as said, subtotaal is not stored.

How should i proceed?

Thanks!

View 2 Replies View Related

Calculating An Age

Jan 6, 2005

If I want to work out how old all my patients will be on the 01 Feb 2005 then why doesn't the expression (in a query)

=(01/02/2005 -[tblPatient]![DateOfBirth]))/365.25 work?

How would I get the answer expressed in months?

View 1 Replies View Related

Previous Working Day

Jun 6, 2006

how do i put criteria into a query to only retrieve records from the previous working day, but if the previous day is saturday or sunday (current day monday) it knows to go back to friday and ignore weekends as they are not working days.

thanks in advance guys

ian

View 6 Replies View Related

Previous Record Value

Jul 16, 2006

Is there a way I can grab the previous record's value without using dlookup? I am trying to create a running total in a datasheet view. e.g.

Date Credit Debit Run. Tot.
6/1/06 $25 $0 $25
6/4/06 $30 $0 $55
6/9/06 $0 $50 $5

I could do it by using an autonumber but the data is sorted by date and not by the order in which it was entered. Suggestions? :o

View 4 Replies View Related

Subtract Previous Value From Next Value

Feb 16, 2007

:confused: Need help building a query that will subtract the previous value from the next value at each change in date

Date Tier 1
1/6/2006 4.22
1/13/2006 4.27
1/20/2006 4.27
1/27/2006 4.314
2/3/2006 4.314

for example, 4.22-4.27 = -0.05

View 9 Replies View Related

Previous Record

Apr 13, 2007

I have an inventory report that I am given among the fields are
Item, Ship Date, Signed Quantity, Filled-Recvd, On Hand
I am given an initial amount in the onhand field but the fields below that for that item are blank.
What I would like to do is [Signed Quantity] - [Filled-Recvd] and then add that to the On Hand from the previous record and place it in the on hand field..

If it were in excel I would say G2 – H2 + I1
G = Signed Quantity
H = Filled-Recvd
I = On Hand
TIA!
Scott

View 1 Replies View Related

Previous Month

Apr 24, 2007

I searched but could not find a simple answer.

I want to see all results from last month for [TestDate]

Month(Date())-1) ? ? ? ? ? ? ?

View 3 Replies View Related

Cancelled Previous....

Aug 15, 2005

Hi there, I quickly made a database, and for some reason, can't get past this 'cancelled' error, to get it to occur, open the Companies form, click orders and then it pops up.

By deleting all of the data in my tables, I can get to a state where I can once again enter data, but after closing the orders subform and opening it again, once again I have the dreaded error.

If anyone has any ideas of why this may be happening, feel free to share them :P

Thanks alot,
James Prince

Edit,

Here's my table structure:

CompanyID PK Autonumber - Relationship
Name
Address1
Address2
Town
PostalalCode
TelephoneNumber
FaxNumber

OrderID PK Autonumber - Relationship
CompanyID - Relationship
WeekNumber
Price
PriceWithVAT

MPL Autonumber
OrderID - Relationship
PartNumber
Size
PriceEach
PricePer100
Quantity

Basically I used the forms wizard to create a linked sub form, and when moving to a new order thats when it errors I think.

View 1 Replies View Related

Keep Previous Data

Mar 20, 2006

Hi Guys,

I'm pretty new in the access world and I'm trying to build my first Database! I've a question (I bet really simple): I'd like to know how it is possible to keep previous data that I've entered in a form when I open it a second time.
Thanks a lot for you help

View 3 Replies View Related

Autofill From A Previous Value

Sep 1, 2004

Okay,

What I need to do is two fold. I have a table in which I track clinets coming in and using our services.

Question 1: I want to be able to have a a date field default to the date I first type in when I open the form and remain there in all
subsequent records till I change it. The data is only entered once a week and we usually have 400 client visits per week
with about 75 - 120 per day. So it kind of gets repeatitive to keep typing in the date. I am aware of the CTRL+', but the staff who use this database do not even want to do that.

Question 2: Next problem....How would I create an autocorrect function that will automatically fill in a name...i.e. I begin to type jo and it will automatically fill in the hn...but I want the values to come from previously entered data from past records, similar to the way Excel does it in spreadsheets.

Any Ideas would be greatly appreciated.

View 1 Replies View Related

Calculating Totals

Jun 28, 2005

I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.

Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.

Below are the 12 scenarios:

1.) Reduces 75% at Age 70

2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000

3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.

4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.

5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%

6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.

7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.

8.) Terminates on the first of the month following the 65th birthday.

9.) Terminates Age 70

10.) Terminates on date of retirement or 1st or month following 70th birthday

11.) Terminates Age 65

12.) Never Reduces

Thanks for your time, Brian

View 2 Replies View Related

Calculating Averages

Nov 5, 2006

I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.

View 2 Replies View Related

Calculating Timespent Help Need

Dec 21, 2006

Hi,

I am new to Microsoft Access (2002) and the Access Word Forums. I am in need of a way to calculate timespent on six main topics through an 8 hr. working day. If I worked on Topic "A" from 8:00am to 8:15am, I need it to calculate it to 15 minutes. Then I need to figure a way to calculate how much time was spent doing Topic "A" for the working day, then by the workweek, then by the month.

I'm willing to learn, if someone has the time to teach or can direct me to a link that discusses something similar to what I am looking for. Any and all suggestions welcomed. I am looking forward to doing this in a timesheet format if possible.

Thank you,

Marty6

View 2 Replies View Related

Calculating Across Tables

Mar 17, 2008

Hi,
I think this should be simple, but I am struggling to work it out

In a relational database I have a stock table containing stock and quantities, there is a customer table with an order table used as the link table also containing quantities.

What I am trying to do is place an order using an order form linked to the order table, automatically reducing the stock table by the amount ordered.

I have tried an update query with no success, also tried using the builder to make a query and linked it to the stock table with no success.

I would be grateful for any ideas, I am sure it is relatively simple

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved