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.
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.
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.
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 ....
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".
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.
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.
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?
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
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)
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.
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
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
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
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.
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
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.
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.
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
