I need to have a field in a database that will calculate the age of a person, based on their DOB. I have it in the excel sheet that I imported into Access to create the data base, but it just copied the number that was already there. When I add new entries into the database, the age isn't calculated. How do I do this?
The forumla I use in my spreadsheet is: =DATEDIF(O2,TODAY(),"y")
I need to assign those appraisers to an order based on what county they cover. My main table (orders) is simple. Just an order number, order date, county and appraiser.I have a form with entry fields for all these except appraiser. I envision a "assign" button that will then pick the next appraiser in the county that was chosen. It's just a "round robin" type of thing, so the first order placed for Monroe would be assigned to ABC. The next order would be for Cecil and would assign 123. Next order for Monroe would assign DEF. I know there are many way this can be done but I've been looking at this for hours and I'm drawing a blank on the easiest way to do it.
View 5 Replies View RelatedHi all ,
I am trying to calculate a person’s age.
Went to Customer Form
Have a field called cust_birthday ( date/time ) ( 99;00;00;>LL;0; )
Have a field called cust_age ( Number )
Went to Madules / New / then add this code,
Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
Dim dteBase As Date, cust_age As Date, cust_birthday As Integer
If IsMissing(SpecDate) Then
dteBase = Date
Else
dteBase = SpecDate
End If
cust_birthday = DateDiff("yyyy", dteDOB, dteBase)
cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
Age = cust_birthday + (dteBase < cust_age)
End Function
Not Working get no return. Can someone help me. Thank You if you can….
John
I'm trying to calculate a value for a field based on the value of another field, [Field1] has a value list of 28 choices I want [Field3] to take that value and multiply it by the value of [Field2]. I'm using an Iif statement and it sort of works. I looks like this:
IIf([Field1]=1 Or 2 Or 3 Or 4 Or 11 Or 12 Or 13 Or 21 Or 22 Or 28,[Field2]*0.06,IIf([Field1]=5 Or 6 Or 14 Or 15 Or 23 Or 24 Or 29,[Field2]*1.1,[Field2]*2.1))
I receive nor errors but it will only return the value of [Field2]*.06 no matter what is selected in [Field1].
I'm sure I'm missing something and there is probably an easier syntax to use, but I'm at a loss at the moment.
I have a form, where one field is a combo box
The combo box is two columns linked to at table,
task and sla
You pick the task, and it displays the task
What i need is, another field, to equal the SLA part of that task.
How can i have multiple people entering data into a database, also there is an excel file that imports the info every few min, if i have that open i cannot enter data into the database. theres going to about 4-5 users plus the excel file.
View 1 Replies View RelatedI have a form where one person wil insert a new record and click on a button that will display his login name and date she/he inserted the new record. A second person will later check if everything was entered correctly and this person will also press a button that will insert his login name and date in a field.The thing is that the same person can't do the quality check and insert the new record. So if in the field "PersonPassQA" is filled in and the person will do the same in "PersonQA", she/he should get a warning and the field should stay empty. This is the code I use to pass the PersonPassQA:
Code:
Private Sub Command24_Click()
'Date pass to QA and person passing to QA
Me.[DatePassedQA] = Date
Me.[PersonPassQA] = Environ("Username")
Me.Refresh
End Sub
[code]...
I have a drop down box in my access database which I want to use to give completion dates to jobs, the drop down has three values:
Emergency
Urgent
Routine
emergency equates to 1 day
Urgent 7days
Routine 28days
I have had a dig in the help files and I can get the order completion date to show the terms emrgency, urgent and routine, however I nned to calculate the date, not just show the term, this where I lose track of how to get any further, I have searched the forum and cannot find an example, but perhaps I am not using the right search criteria, I have tried date calculation on drop down results etc etc and no dice, I am sorry if I am posting a question whichg has already been answered, and if anyone can post a link to a solution that would be great to.
Many thanks for your help this is my first post on this forum, but I am sure I will be here for a long tme to come access is fast becoming my life :D
again many thanks for taking the time to read this and I appreciate any help you can give me.
I have a drop down box in my access database which I want to use to give completion dates to jobs, the drop down has three values:
Emergency
Urgent
Routine
emergency equates to 1 day
Urgent 7days
Routine 28days
I have had a dig in the help files and I can get the order completion date to show the terms emrgency, urgent and routine, however I need to calculate the date, not just show the term, this where I lose track of how to get any further, I have searched the forum and cannot find an example, but perhaps I am not using the right search criteria, I have tried date calculation on drop down results etc etc and no dice, I am sorry if I am posting a question whichg has already been answered, and if anyone can post a link to a solution that would be great to.
Many thanks for your help this is my first post on this forum, but I am sure I will be here for a long tme to come access is fast becoming my life :D
again many thanks for taking the time to read this and I appreciate any help you can give me.
I am trying to write an expression that calculates a new field in a query. The new field is simply recoding values from another field (survey question responses) using IIf statements. This new field, let's call it RecodedResponse, will eventually be summed in a totals query to get the total score on that survey for each participant. Each record is a question response and there are multiple records for each participant (16 items on this particular survey). The problem is that for 2 of the questions on this 16 item survey, the responses are supposed to be eventually combined and recoded based on each other. So, my question is:
Is there a way to do a conditional expression where the result from the previous record is used? Or do I need to first do a crosstab query so that all the responses for one participant are on one line?
I am pretty sure I can do a workaround to make it work, probably doing a crosstab, then recoding in another query based on the crosstab, but it would be cleaner (I think) to do this some other way. Any ideas? Or is this too vague?
Thanks,
Liz
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 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())
I am not sure if what I am about to ask is best done in a query, table or report, so I thought I would post this in the query section of the forum.
I have a table that contains the following fields (plus other fields)
RC_START_DATE
RC_DUE_DATE
RC_APPROVAL_DATE
RC_STATUS
On one of my reports, I want to show the "Days Open". If the RC_Status is "Active" I want to calculate the "Days Open" by subtracting RC_START_DATE from Today(). However if the RC_Status is "Closed" I want the "Days Open" to be calculated by RC_APPROVAL_DATE minus RC_START_DATE.
So the question is, what is the best way to do this and how would I write the formula to calculate this?
Thanks for the help.
Jim
Hello,
Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?
For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.
So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).
Any help would be greatly appreciated.
Thank you
I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".
The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).
What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.
I am trying to create an attendance database, our company introducing the point system attendance,
Called Off (CO) 2 points
Left Early (LE) 1 Point
Tardy (Tar) 1 point
Employee can reduce point if they have perfect attendance for 90 days from the last day of violation. For example, an employee absent on 01/01/2015, he will received 2 points, the credit will giving on 04/01/2015, if there is no violation, but if he absent again on 03/31/2015 not only he will received 2 more points his 90 days will start from 03/31/2015, now he will eligible to get credit on 06/29/2015 and so on.So far I have created 3 tables and 1 query.
Tables
Employees: Id, Last Name, First Name
Points: Id, Description Points
Attendance:Id, Date, Employee ID, Points ID
Query
Date
Employee ID
Last Name
First Name
Point Description
Points
how and which formula to use which calculate the points based on above example.
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014
End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are:
1 - April 2014
2 - August 2014
3 - December 2014
4 - April 2015
5 - August 2015
6 - December 2015
Suppose that you want to make a formula which will calculate time periods based on a given tariff. To be more precise.
Suppose you can "get in" somewhere, entrance is $2.
You are free to stay there, without additional charge, for 5 days.
Then, for the next 5 days, the charge is $1 per day
The next 7 days, the charge is $1.5 per day
Thereafter, $3 per day.
In this scenario, we can make a query, we use datediff function to see the number of days between "gate in" and "gate out".
The problem is that the tariff is not very simple and the query gets to big and difficult to control with lot's of "iif" and may be I need to make more than 4 queries and union select and so on.
I tried to find in the docmd if there is any suitable function that I could use.
I created a database to record time logged per workorder for each employee on my job. Each time log has a specific "Trade" attached to it along with a number of hours the employee spent on that workorder. I've created a report to display how much time the selected employee spent on each workorder (within a date range) and now I want to see what percentage of their time was spent on a particular "Trade" (for instance, during September Employee "name" spent "percentage" of their time on Electric, "percentage" on HVAC, "percentage" on Plumbing...[and so on])
I have trades listed in the table and in the time log, the form writes to the trades area of the table (probably very elementary for this discussion) and the report lists the name and grand totals with percentage of total time on each workorder, but does not list any trade information.
How can I add this into my report, preferably at the end (Report Footer?)
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
In my table I have the following 4 fields with the associated date field:
Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015
I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?
=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))
I would like to create an Access Database that calculate due date based on 2 tables, one with invoices and supplier details. The other one with Supplier name et payment terms.
Input:
Table with the following details:
- Supplier Name
- Invoice Date
- Currency
- Amount
Output:
Table same as input but with a column that shows for each record the Due Date. The due date would be calculated by adding payment terms in days from another table to the Invoice date.
I have an order database Access 2000 for an inspection company, tblInspectors, tblOrders, tblCustomers, I want to be able to calculate inspector pay within order database. Each inspector is an independent contractor, so it just needs to calculate percentage, for each inspection performed and total for pay period. Each inspector is paid a different percentage, which is stored in tblInspectors. Payroll is paid every two weeks . . . I've been trying to figure out the best way to do this ... :confused: Any advise be greatly appreciated.:confused:
View 4 Replies View RelatedAccess database to calculate income tax?
I'd like to be able to input the amount I invoice each month into a table and it would calculate Vat and Income tax due.
I'm sure its been done many times before however I cant find any examples.