Hello,
I am trying to work out how to find the difference between two dates, in years and days. I have a [date_of_birth] field and another field [date] and wish to work out the difference in years and days as opposed to decimal years. Is it possible to do it?
Thanks.
Hi there,
I have long file (1 mil records) downloaded from AS400 as .csv text file.
there 2 feilds - shipping date and receiving date.
I need to calculate the lead time in days as RecievingDate - (minus) ShippingDate.
It would be easy in Excel, but i have too many lines.
So there are 2 problems.
How to make text data from .CSV the date in Access?
How to calculate using date data in Access?
Thanks to everybody interested to help.
I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:
[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue
What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.
I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.
View 14 Replies View RelatedI have a situation where I need to extract the date from a text field to use in a select query. The date in the text field is listed at the beginning of the text field like this.
S12345678 3/21/2008 adittional text beyond here blah, blah...
the first series of numbers can be either 9 or 10 digits long, and then the date is always in this format, the length of the characters change accordingly with the date. So trying to use the Mid function only wont work.
I supposed I need to find a way to get the position of the space character in front of and after the date to use the Mid function for each record.
I need to be able to extract this date to do a DateDiff against another date.
I have been trying to do something like this.
Narr_Date: Format(Mid([si_narr_t],InStr(1,[si_narr_t],Chr(32)),InStr(9,[si_narr_t],Chr(32))),"Short Date")
But I know I am off the mark here. Can someone tell me what I am missing?
I've had it before, but can't find it.
basically something like this --
DateDiff("w", StartingDate, EndingDate)
that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?
I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?
Thank you for your help.
Can't figure out why dlookup will not work for me in a query for certain dates?
Simple example to highlight the problem:
Table1 -
Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40
Query based on this table with the following calculated field:
Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")
Query output -
Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40
Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)
If you try it with many dates its skips several and I cannot see a pattern.
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
[code]...
The 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!
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
View 5 Replies View RelatedI am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1
[code]....
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
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 1 Replies View Related I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
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 an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
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 have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.