I have just started using Access 2010. I need to calculate student's ages from their date of birth to today's date. I have a column set up for the date of birth but can't figure out how to get this figure.
View RepliesI have a [DOB] field with birth dates. I have another field [DOBExp] where I want to automatically calculate at date 15 days out, but exclude weekends and holidays. I have a separate table listing the holidays.
Would like a formula to use in my first table for this. Have no clue how to do this.
I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.
Thanks
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
Calculating dates between TWO date ranges:
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
Don
I am tring to add a number of years to a dob. I'm doing this by adding my
date+years*365.26 I get a string of numbers. I then convert the number in
the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.
An example would be:
DOB age date I need to follow up
5/15/1935 + 75 = 5/15/2005
Then I request dates between 4/1/05 and 5/30/05 and this date is returned.
I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.
I've tried: Adding a column to my query to convert the number string back to
date format (criteria failed to return a result). Adding a second column
that ='s my converted number to date column (criteria failed to return a
result)
And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data.
Is ther an easier way to: Add a number of years to a date and have it return
a date and not the number string?
If not is there a better way to convert the string to a date?
Is there a way to get my criteria to recognize the date and return date
between my begin and end dates?
Thanks
In my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
Any help will be appreciated, thanks
I am building a database and am trying to automate month names and years to appear in my table upon adding a new record. I have the code built to input the data I am calling from other tables, and have functioned it to work on a button click. Now I want the record to capture the current date and year everytime a new record is added and input that information into the respective fields.
View 3 Replies View RelatedI create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.
View 1 Replies View RelatedHi,
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
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 two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I need help. I want a field in my table that generates an Log number.
I already have fields:
I.D. number (sequential number)
Project Name (Text field)
Milestone (Number field)
The Log number should be made up of the first two letters from the Project Name, then the Milestone, then the ID number. The three parts of the Log number should be separated by hyphens.
So if:
I.D. number = 01
Project Name = Nice Light
Milestone = 03
Then Log number would = NI_03_01
On my form I have managed to get this to happen using an unbound calculated text box, but I need the log number displayed in the table, and this seems impossible.
Any help? Important things to note are I am not very clever when it comes to computers, or programming.
Cheers
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies View RelatedI need to set a field so that only people that are 16 years old from the current date can enter their birthday into the field. I am assuming that it is a 365-day year. I know that i need to use the DATE() function and the number 5840 (16*365). I can't figure this one out, HELP PLEASE!
View 3 Replies View RelatedCurrently I have a form with these variables
- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time
In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.
What I have already done is allocate shifts per asset e.g.
when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM
also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
These are the scenarios that is entered through a "Form"
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".
I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.
Here are some snip it
snipit2.png
snipit.png
If I have a Date Of Birth in the form XX/XX/XXXX, what code do i need to use in the Design View CRITERIA Box. Thanks for all your help!
Richard J
I have a "Client" table which has a "Date of Birth" field and the problem I am having is on in the data entry form the users type in "2/25" instead of "2/25/1965" on for the most part that's OK. But if the client DOB is 1930 or before then 2/25/2029 comes up.
I have found that the date format is tied to the Windows Regional settings and we are working on a network that doesn't allow me to adjust the settings.
I have tried to put a validation on the table for the field but then that makes it so you HAVE to enter something and can not leave that field blank if you want.
Any ideas?
Hi i know it is simple but can someone tell me how to work out an age of someone using their date of birth and the date now please. thanks i cannot remember the formula.
View 1 Replies View RelatedHI everyone, and thankyou for reading this.
I am the secretary of an athletics club and have the task of using membership data to compile groups of athletes.:eek:
One of the fields that I have available is the date of birth of every athlete. It is entered in the form dd/mm/yyyy.
I would like to be able to query the database and display the age of each athlete.
I would also like to be able to query the database so that I can compile groups of athletes grouped by their ages as at 01/09/yyyy
These groups would be: Under 11 years,
over 10 years but under 13 years,
over 12 years but under 15 years,
over 14 years but under 17 years under 20 years.
over 19 years.
I have some understanding of using SQL view in Access but my overall knowledge must be viewed as "novice".
If you are able to help me with this problem I would be extremely grateful (as would our club), and I would also be obliged if you could keep instructions simple.
Many thanks
John:)
This is my first post to this group. I am building a database for a charity event and need to calculate the age of a person based on the Date Of Birth field in the table and add that number to a separate column in the table. Also, need to calculate the age as of a certain date (the beginning of the event). Thank you for any help.
Larry
I am a novice trying to create a school access 2000 database, I have a table that contains a DateOfBirth field and want to convert this field into Age (automatically add a new field), so i can then create a form from it and filter the pupils by age.
Ive read post http://forums.aspfree.com/showthread.php?p=112241 which is exactly what I want to do, but it doesnt go into the basic s, like where I put this code to get the field to change.
Please help
Thank you
I've a club database the records score for juniors on age groups that are set as of the 1st January each year. I would like to set this automatically from the date of birth. I have found some code that is very close to what I need apart from the fact it groups on the exact DOB resulting in changes to age group mid year.
'----------------------------Code Start--------------------------------
Public Function AgeGroup(dtmBirthDate As Date) As String
Dim intAge As Integer
'Age Calculation
intAge = DateDiff("yyyy", [dtmBirthDate], Now()) + _
Int(Format(Now(), "yyyy") < Format([dtmBirthDate], "yyyy"))
Select Case intAge
[code].....
I am using Access 2007, and am new to Access.
I would like to calculate the Age based on the Date of Birth , which is in dd/mm/yyyy
I use the below expression in Query but I get back #Error for most records; whereas the Age appears correctly for a few records.
Age: DateDiff ("yyyy", [BirthDate], Date())
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
I have this code for calculating the age from birthdate but i want if there is years no months or days appear
Public Function CalcAge(Birthdate As Date) As String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
intMonths = DateDiff("m", Birthdate, Date)
intDays = DateDiff("d", DateAdd("m", intMonths, Birthdate), Date)
[Code] ....