I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View RepliesMorning all
I am currently working with IT and am looking for a course/courses to further advance within the IT sector.
My employer is willing to fund and/or allow me time from work to obtain the qualification.
I am writing to see if anyone could advise on the best courses i should be looking at or are available. I am looking into doing something with SQL and have been looking at the MCITP course with Computreach.
Can anyone recommend this course or has anyone any other recommendations?.
Regards
Nathan
Hi,
I'm new to this forum so forgive me if this thread is in the wrong section.
I'm currently creating an OHS&W (Occupational Health Safety & Welfare) database. My boss has just requested if I can possibly have it so that when a Employee has a job title selected for them it automatically lists the courses that are required for that job.
Can anyone help me please?
Cheers
My database includes two separate dates related to a vehicle purchase and default payments resulting in repossession. I need to find out how many weeks passed between those dates.
Example:
03/14/12 Date of automobile purchase
06/06/13 Date of last payment before default
How many weeks in between? And then I need to average all of those figures so my company can see the usual week-term time frame that our customers are defaulting and getting repossessed.
I have the attached database which calculate the Difference between dates.I have also two problems like i shown in the image
Code:
Option Compare Database
Option Explicit
Sub Recalculate()
Dim rst As DAO.Recordset
Dim dff As Long
[code]...
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])))
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.
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],
Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
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 RelatedI need to show field values in relation to dates, but I want the dates to be shown horizontally in the report.
I tried the crosstap quarey but it is not working for me, as i need to report many unrelated raws.
I've attached brief explanation of what i want.
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
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 am very new to access and I would like to pull some information from my tables.
I am using this for training records, I can calculate the expiry date of certificates using a query. What I would like is a report (even better an alarm of some sort) to show when things are due to expire. I can't seem to find this option as a report.Also, is it possible to have reports go into excel?
Help Help Help - I have a database which I need to calculate a date 3 years from now, then report on it 6 months before the 3 years expires...
Hope that makes sense
Fay1136:confused:
:confused: DateDiff gives me the number of days between 2 dates but how do I count only WORKING days between 2 dates?
I hope someone can help?
Cheers
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 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)
I am woking on a project full of nightmares. My latest, and the one I am currently unable to solve, involves trying to calculate the number of days between Billing Dates.
I have a table named "Date" - I didn't design this!
It contains a field named "Date"
If I query for just that Field I get the following:
Date
9/8/2005
8/8/2005
7/8/2005
6/8/2005
5/9/2005
...
I need to figure out a way to determine the number of days between the bills so I can calculate the average cost per day.
It seems like it should be an easy thing using like the datediff function or something, but since they are in separate records (rows), I can't figure it out. I have tried and tried and tried.
Any suggestions would be VERY much appreciated.
I have a database with 10-12 fields containing dates certain things were completed. There is also a field for the 3 different locations we have and a program year 2006, 2007, or 2008. What they would like to do is click a button and get a list to choose the first date they which to compare, then a list to choose the second date, calculate the number of days between the two dates, select the program year from a list, and a location from a list. Can anyone help me with how to do this if it's possible?
The fields look something like this:
Prog Year Location DateA Date B DateC DateE
2006 G date date date date
2006 G date date date date
2006 P date date date date
2008 D date date date date
2007 P date date date date
For Example, I might want 2006 Program Year, Location P, calculating days between DateA and DateB, except that the next time I might want to choose a different set of criteria.
Any ideas?
THANK YOU!
I'm trying to create a sub to count how many days between 2 dates excluding Holiday and weekend. Using the DateDiff() function is not allowing me to exclude certain days.
View 2 Replies View RelatedThe below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
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
What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).
The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.
The formula will then be:
simpledate - convertedcasenumberdate = days between.
(simpledate will always be the latest date of the two).
We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.
View 14 Replies View RelatedI have a form with date fields I need to calculate review dates for employees. Example 45 day 60 day 90 day what would be the best option to do this?
View 1 Replies View Related