age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
Dob is stored yyyyddmm
I 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
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 Relatedhi 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!
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?
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
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 RelatedHi,
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
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
Hi Guys,
New to the forum, and its nice to see how active this place is, and upto date.
My question is, and probably simple to correct. ( Please be patient with me, i`m new to this, and my terminology might be incorrect, also please simple answers )
I`ve searched the forums, but as my teminology is wrong I cant find the results im after :)
Anyway, I have a table with many fields in it, but the parts i`m having problems with are as follows.
I have fields with
Price Inc Vat, Deposit Payment, Stage Payment, Balance Payment & Balance Due.,
The part im trying to get to work is the balance due which I want to automatically update, based in the data inputed into price inc vat deposit payment etc.
The calculation i`m after is as followes
Balance Due = ( Total Ammount Inc Vat - Depost Payment - Stage Payment - Balance Payment )
eg, Total Ammount Inc VAt = £2000
Depost paid is £1000
so far balance due would be £1000
Then Final Payment is made 2 weeks later £1000
So balance due would then show £0.00
Is this possible
Thanks in advance
If I have not made myself too clear, please excuse my crapness, and anyhelp Is much appreciated.
If you need any more info, please do not hesitate to contact me on
max.vernon@claddagh-group.com
Thanks in Advance
Max Vernon
[edit]
Just to let you know, I also need these details kept in the table for future reference so I dont think I can use a query to do it.
I`ve attached a copy of the database with some example data in it if this is needed for any assistance.
I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)
View 3 Replies View RelatedHi I'm building database for a car shop. One of my table (Cars) has these two fields in it intModelPrice (which stores factory price of a model) and intPriceWithTax (which value should mathematicaly be intModelPrice+[(intModelPrice)*(18/100)]).
Now, can I set my default value of intPriceWithTax to be calculated by this formula and how? I tried some combinations but I got various error messages.
I red Tips section on your site and saw that default value can't be calculated. I know how to get what I want in the select query but I want this table field (intPriceWithTaxt) to be acctualy filled with number value each time I enter a new record in this table (for a new car model). How do I acomplish this? Thanx.
P.S. - I searched the forum for answer but I had no luck.
Hello All!
I have data laid out as follows:
Measure MonthYr Value
Fixed Std Jan04 90%
Fixed Std Feb04 95%
-------- ------ ----
-------- ------ ----
Fixed Std Jun05 93%
My situation is two-fold: 1-I'm having difficulty coming up with a query that will calculate the weighted avg. of each quarter in the prior year and current year, 2-what to do about the current year's quarter when only 1 or 2 month's worth of data exists?
Can anyone help - it would be greatly appreciated. Thanks.
I have a table containing the following fields :
Sex(text), Age(numeric), sodium concentration(numeric).
I am trying to write a query which will return the percentage of male patients have a sodium concentration of a certain range (ex:120-130) out of the TOTAL number of patients.
I am able to count the number of events which fit the criteria but I cannot figure out a way to calculate the percentage part (unless i manually add a field in which I devide the count result by total number of patients).
Any ideas?
Thanks
Hey Guys,
It’s been a while but I have been visiting from time to time. Just been busy with a load of web design.
Anyhoo, I have been asked to create a report on total revenue generation for projects and have taken it as far as I can, and now need your help.
I have two queries “qryCommTrialsRevenue_PatientEvents” and “qryCommTrialsRevenue_StudyCharges” that select income via two cost codes and between two dates.
Two further separate queries calculate the total for each project. That’s as far as I can take it at the moment and now I cannot see the wood for the trees…I need to calculate the total for each project ACROSS the tables. Can this be done successfully? Every time I try it the numbers don’t add up.
Cheers,
Rusty
:D
I have a question I need help for, below is a very strict detailed explination of the current scenario I am in and I need assistance for it:
1) I have a percentage of Commission I need to calculate, the percentage of commission constitudes of the following information:
a) Date,
b) Customer Name,
c) Company,
d) Name,
e) 5% New,
f) 3% Ext,
g) Rep,
h) Doble Ref (0.025, 0.015),
i) Total Amount, and
j) Total of Commission.
If a person enters the same customer name more than ones, it will give that person's name a Doble ref (0.015) of commission, if on the other hand that customer is new it will give that person a commision of 0.05% new. I hope this is not intending to confuse you, it just gives the general idea of what the percentage of commission is intended to be.
To come back to the point, I have a form created under Access that holds all the names of the person's reference by in that case "Name" letter (d) described above. If I select for example a person name "Fahed" and I click on the button calculate new commission I want to be able to draw all the commissions that Fahed have done and then use the form above to do the approriate calculation.
The above is a general problem, below is the scenario:
I get a form called New Client, the form New Client is a parent form and underneath that form is a sub form which holds estimates, invoices and so on. Ones again I am giving you the picture of what the form looks like, I cannot paste the pictures here because they would take time and would not be clear. The new Client has a text field called Reference By. That text field is where I need to get for the Percentage of Commission form to be entered under the field Name, letter D (described above). The total of amount is extracted from the subform Invoice, total, of the parent form called New Client, and there is a check mark in the new client called "Already existed" that if I click it, that information is then send on a separate form called "PercentageOfCommission" that will determine if it calculates the percentag of 0.05 or 0.025 depending if it is Calculate Commission or calculate existing commission.
I want it to be able then to do the needed calculation which I already have the approriate formula for and then display in a report what is shown above.
I don't know how to get information from different forms, and I don't know how to display for example ALL FAHEDS with information but are related only to FAHED.
Ones again, let me give you an example:
Date Name Info
15-Feb 05 Fahed Stonyx
15-Mar 05 Fahed Commodore
...
18-Sep 05 Ahmed Apple IIc
I want to display only one name Fahed and when I click for example display summary to show ALLLL the name that holds Fahed that have different Info. Any help would be greatly appreciate it.
If you feel what is posted here is TOO confusing and trying to explain it on the messageboard might take time and you wish to assist a poor soul in need, then you can contact me at devastating_battle@hotmail.com so we can talk live in MSN to be able to describe the matter much clearly. Please don't ignore me and leave me hanging in the air dry, thanks in advance.
hi all,
i need your help on how to calculate non-value item, in this case a textbox fills with course names & description. please refer to attachment for more information.
thanks
I have a table "main" with 2 colums (main_id,date_out). This table has a link to the table "refill". This table has 3 colums (refill_id, main_id, refill_date).
Example data:
TABLE MAIN
main_id - data_out
01 - 01/01/2006
TABLE REFILL
refill_id - main_id - refill_date
01 - 01 - 10/01/2006
02 - 01 - 16/01/2006
03 - 01 - 21/01/2006
When I make a simple selectquery with the fields main.date_out and refill.refill_date I become the the next result.
01/01/2006 - 10/01/2006
01/01/2006 - 16/01/2006
01/01/2006 - 21/01/2006
Now the problem.
I have to calculate the days between the main and refill dates
01/01/2006 - 10/01/2006 ( is the same as above)
10/01/2006 - 16/01/2006
16/01/2006 - 21/01/2006
suggestions????......thanks
i'm trying to create a query which looks at the field dateEntered and then returns the previous sunday date of that week.
ex.
dateEntered returnedSundayDate
09/26/2006 09/24/2006
are there built in access functions to figure this out in a query?
please help.
Hi folks,
I would like to add up all the records in a field to get a grand total. Can I run a query to do this? I've tried the Totals button, but doing this is just summing the individual records, not all of the together.
Thanks for any help you can suggest to this Access virgin.
HI there,
I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.
I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.
Hoping someone can assist.
Many thanks
Hi there,
I need some help with calculating login/logout times. I have an ODBC link set up for the raw data in AVAYA. The problem is that the login/logout times are reflected as the number of seconds that have passed since January 1st, 1970. I need to know what that amount in seconds is in a time format.
I have worked out the number of days difference between my rowdate and the start date of 01/01/1970 so I am left with how many seconds have passed since midnight of the selected date.
For example, the selected date is 3/7/07. The Login time is 5410 (seconds passed since 3/7/07 00:00:00). How do I figure out what time that was (hh:mm:ss)?
Any help would be greatly appreciated.
Rick.
Is it possible to use the result from a calculation in a query as data for a field in the table the query is based on?
Basically, I have an event that has a start date and an end date. The table stores individual events and the query only shows those events that have a start date but nio end date.
Also, each event can restart later with a new start date and end date.
I want to use the dates to calculate the number of days duration for the event's instance and store that in the table for later use - each event is allowed only a certain number of days.
Then, I'll need to add up the number of days for each instance of an event, to see if it has reached the maximum allowed.
So, like this;
Table Fields:
eventID, eventtype, startdate, enddate, numdays
As soon as I enter an end date for an event, I want it to calculate the number of days since the start and put that in the numdays field.
Then, I'll need to add up all the numdays for each eventtype.
The part I'm really having trouble with, though, is just the part to do a calculation and put the answer in the table that the query is based on.
In the event builder I tried [numdays] = [enddate] - [startdate], but it always drops the numdays part, leaving me with = [enddate] - [startdate].
Any ideas?
I am calculating the total sales using the formula, in a new field;
Total Sales: ([Quantity]*[Price])+[postage]
This works fine and adds up the totals of each individual sale. But, what I really want to show is the grand total of all sales (the total of all figures in the new "Total Sales" Column)
Thank you
I created a crosstab query to give me the aggregate totals for each of my 10categories over 3 years. Categories going down, years going across. However when I try to create a report and sum the 3 years I don't get a true sum it adds the fields togethers. Example.
2004 - 0
2005 - 1850
2006 - 0
I get 01850. I need to get a true sum. Do I need to create 3 separate queries to bring the actual values in to do a calculation?
Thanx.
i have query tthat calculate perioe between date. the result is number.
how can i modify so it will show something like 1 year, 2 moths 3 days?
Thanks
Hi,
Wonder could anyone help me…haven’t used Access for a while and I’m a little rusty :confused: :confused: I am trying to run a query where I need to calculate the total amount of money received between two dates.
My field are DateReceived and FeeAmount. For example I need to calculate the total amount of fees received between 01/01/2007 & 01/04/2007.
Any help appreciated