IIF Statement - Calculate Value Based On Comparing Current Date To Dates In Fields
Jun 27, 2013
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"))))
View Replies
ADVERTISEMENT
May 6, 2013
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.
View 2 Replies
View Related
Sep 22, 2011
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.
View 14 Replies
View Related
Sep 11, 2013
I use this
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
View 4 Replies
View Related
Sep 28, 2012
The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.
Table_1 with below column
Mode
6
6
6
6
6
4
4
Primary KEY
0001
0002
0003
[code]....
Initially the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,
However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.
Once I have sorted this out, I would like to achieve this as the calculated field
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
how I can do this?
View 6 Replies
View Related
Aug 18, 2015
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
View 3 Replies
View Related
Sep 27, 2012
query is refering to 2 tables
Table_1 with below column
Mode
6
6
6
6
6
4
4
Table_2 with below column
Type
06
GL
PL
04
16
Both tables are joined, and the pseudo code i'm trying to get to work is;
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
I have tried below, but doesn't work.
Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.
Ideally would like this
if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"
or
if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH
View 6 Replies
View Related
Nov 21, 2014
I'm trying to calculate the number of days between two dates using the iif statement
Fields:
[LDW] "Last Day Worked"
[ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
View 2 Replies
View Related
Jun 15, 2015
I'm trying to build a query with an iif statement to calculate expiration dates within a query. For some reason what i'm trying to do is not working as expected.
Here's the situation. Every employees with an exception of two employees in the company has an expiration 2 years after the class took place. The two exceptions are the certified instructors who's training certification is good for three years.
I've created a query with the employeeid, training subject, and maxoftraining date.
I'm trying to write an if statement to give me the expiration date based on the above information. Here's what I have so far; however its not calculating based on the criteria.
Expiration: IIf([tblemployee]![EmpID]=1 Or 2,DateAdd("yyyy",3,[maxoftrainingdate]),DateAdd("yyyy",2,[maxoftrainingdate]))
At this point in time, all "expiration" dates are showing + 3 years rather than just the Employee ID's 1 and 2 and the rest +2 years.
View 2 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Jun 14, 2007
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
View 3 Replies
View Related
Aug 10, 2015
I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database.
If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date.
Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel
=If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate)
View 2 Replies
View Related
Jul 1, 2015
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.
View 14 Replies
View Related
Jan 16, 2015
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
View 6 Replies
View Related
May 16, 2013
Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?
View 3 Replies
View Related
Apr 22, 2013
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
View 3 Replies
View Related
Jun 14, 2005
Hello,
I am trying to create a listbox that shows "calculated" dates for a two week payroll. I have a field in a table that is set to a start date (05.31.05) and another field that specifies the number of days between dates (14). I would like set a listboxes recordsource to show the start date and then calculate the next 4 dates using the first date (05.31.05) and the range (14) without actually storing any dates in the table. I can figure out how to do it if the values are stored, but I just want to know if this is possible to get the results another way.This may sound crazy and unecessary, but I want to know if this is possible without storing values in the table.
Listbox values:
05.31.05 - (first date from table)
06.14.05 - (first date + 14)
06.28.05 - (first date + 2*14)
07.12.05 - (first date + 3*14)
07.18.05 - (first date + 4*14)
Is this possible? HTMS. Thanks.
View 5 Replies
View Related
Jun 30, 2015
I need to calculate the final date based on the begin date and the number of days.
The name of the fields are: sdatainicio; diasatribuidos; sdatafim.
View 7 Replies
View Related
Jul 16, 2014
I have a database which stores information relating to club members, which I'm in the middle of giving a bit of a facelift and one thing I want to automate is the calculation of the expiry date.
My memberships expire annually at the end of December and the expiry date is set as a default value within the table properties. This has to be manually changed every September (every new member from then pays for 15 months) to the following December.
Is there a formula I can insert in the defaul value field that will return December the current year if todays date is between January and August, and December next year if todays date is between September and December?
View 14 Replies
View Related
May 7, 2013
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
View 3 Replies
View Related
May 21, 2007
Hi,
I have an arrival date and a departure date and I need to work out if the current date is within the two dates. It's a criteria for one of my fields on the query I'm using as the backbone for a list box. Any help greatly appreciated :D
Thanks,
Adzi
View 2 Replies
View Related
Jul 22, 2014
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
View 3 Replies
View Related
May 7, 2013
My first question is how to set a column to be the current date read by the computer.
Example: It will automatically say 5/8/2013 tomorrow without needing updating.
Question 2: How to add a number of days to the current date.
Example:
Add 7 days to the current date.
on 5/8/2013 it will read 5/15/2013, then automatically update to 5/16/2013 on 5/9.
View 1 Replies
View Related
Feb 1, 2006
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.
View 3 Replies
View Related
Feb 1, 2006
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.
View 6 Replies
View Related
Oct 18, 2004
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
View 3 Replies
View Related
