Hi,
In MS Access i'm trying to calculate the difference between one date time to another. for example
26/04/2007 09:00 am
07/03/2007 10:30 am (Current date time)
This should work out the:
days
hours
minutes
seconds
I did want this to be shown on a form
Thanks
Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
View 4 Replies View RelatedHi
I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date
Dim valuation2 As Date
Dim valuation3 As Date
Dim valuation4 As Date
Dim valuation5 As Date
Dim valuation6 As Date
Dim valuation7 As Date
Dim valuation8 As Date
Dim valuation9 As Date
Dim valuation10 As Date
Dim valuation11 As Date
Dim valuation12 As Date
Dim startdate As Double
Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date
startdate = CDbl(Me.Start_Date)
findate = CDbl(Me.Completion_Date)
'calculate evaluation periods
Dim valCount As Double
Dim valDays As Double
valCount = CDbl(Me.Valuation)
valDays = (startdate + ((findate - startdate) / valCount))
'Work on entering valuation dates
Do While valcounter < valCount
Select Case valcounter
Case "1"
Me.valuation1 = startdate
Case "2"
Me.valuation2 = startdate
Case "3"
Me.valuation3 = startdate
Case "4"
Me.valuation4 = startdate
Case "5"
Me.valuation5 = startdate
Case "6"
Me.valuation6 = startdate
Case "7"
Me.valuation7 = startdate
Case "8"
Me.valuation8 = startdate
Case "9"
Me.valuation9 = startdate
Case "10"
Me.valuation10 = startdate
Case "11"
Me.valuation11 = startdate
Case "12"
Me.valuation12 = startdate
End Select
valcounter = valcounter + 1
startdate = startdate + valDays
Loop
' this value is to test the interval period
Me.tdays = valDays
I am new to vb and so ths code is not going to be the best or anywhere near but any help would be great.
Thanks
Hi
I was wondering if somebody could help with a problem I have come across. I am currently developing a database for a small charity that I work for. The aim of the database is to record when staff undertake counselling.
I have created a query that calculates the current age of a client in the database uing the following expresion:
Age: Date()-[Date of Birth]
The Date of Birth is formated as a Short Date
In the query I have set the format of the column in the query to 'yy'. This then just displays the age of a client. This work fine apart from ages which are less than 30. If the age is less than 30 e.g. 29 it displays the date as 1929.
Does anybody know how I might be able to solve this or an alternative way in which to calculate dates and display it as 'yy'. (Its been a few years since I last work with access so skills a little rusty)
The reason behind the yy is that the monthly & yearly stats have to group by age ranges.
I am using Access XP (2002) SP2.
Many thanks in advance
Tony
Hi
I have a field for 'deadline' date and I need to show all records where the deadline date is within 7 days of todays date. (Obviously todays date will change daily...)
I have tried subtracting the date from todays date but it doesnt work.
All help appreciated, thanks
Martin N.
I am trying to work out the time span in days between the day I archived a record and the current date. In simple terms it would look like this:
Me.TimeSpan = Now() - Me.ArchiveDate
Now I know this isnt right, cause I tied it! Can someone put me in the right direction please.
Hi Guys,
Just a quick request for a bit of help.
Tries a few things but didnt work.
I have a form which has an employee start date.
What I need to do is work out the holiday entitlement of each emplyee.
The math behine this is, if they have been here less than a year,
we do the following.
20 Days holiday entitlement per year, Divided by 52, then multiplyed by the ammount of weeks remaining in the financial year.
eg, (20 / 52 = 0.384615384) * 31 = 11.92
So they would have 12 Days holiday entitlement.
But if they have been here for 12 months or more, they are entitled to the full 20 days holiday.
How would I express this as an expression or query or VBA to calculate this.
I just cant figure it out, especially the if been here 12 months or more.
I have the start date, in a tbl called tbl_employees if that helps. ( also in the form frm_employee_details )
Our financial Year runs from the 1st December - 31st November
Thanks in advance for any help / advice
Max
Several questions here, trying to lead up to the easiest code...
First is I have a table with some dates, and I need to do some date calculations, but one of the dates I need will vary. So I was thinking of having a parameter ask for it. Is this the best method?
Second, then once I have this date in, I'll need to have it subtract from another dated reference in the table to figure out the numbers of days difference. e.g. 05/31/06 - 05/01/06 = 30 Can you get two dates to subtract like that and come up with a general number field? If so, then will the dates have to be text, converted to numbers, or can stay as dates?
I've tried several methods and have been quite unsuccessful in each attempt.
Thanks
Hi there
I need to perform a query that works out how far in advance people book holidays. I have the arrival date and also the booking date, and I think I should be using DateDiff. However, I tried to put it in an existing query and it hasn't worked:
SELECT Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings, DateDiff("d", [Booking Date], [Arrive])
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date])
HAVING (((Year([Start Date]))=2004));
Any help most gratefully received!
I am trying to write a query that will return all records whose field expiration_date meets my criteria. The criteria is
If expiration_date - 5 years < today's date then.... do you subtract the total days? months? Any help would be appreciated.
Thanks in advance,
Mark
I am building a database and in it I need to do date calculations. Without too much detail I input a date based on the built in calender from the date/time field and based on that date I need to have the following data returned into other individual fields: 6 months from that date, the days until that date, 2 months from the date input, 4 months from the date input, 5 months from the date input, 60 days from the end date, 45 days from the end date, 30 days from the end date, and 15 days from the end date.
Example: I input "Sep 10, 2015" and I would get "Mar 10, 2016", "181 Days", "Nov 10, 2015", "Jan 10, 2016", "Feb 10, 2016", "Jan 10, 2016", "Jan 25, 2016", "Feb 9, 2016", "Feb 24, 2016".
I have read through websites regarding date calculations within Access but I cannot find the specifics for the application that I need.
I tried this Between (Date()+7) And Date()
But was wondering how do I write this in an IIF statement.
I want to get a text field going for example:
IIF Between (Date()+7) And Date() THEN "Certification Alert"
Hi
I need to know how to avoid from overlapping between times per date when the user is isernt a new data.
I thought to use dllookup function but it finds one result but if there is more than one result.
Date Ent Ext
1/1/2007 8:00 17:00
1/1/2007 9:00 16:00
1/1/2007 10:00 15:00
In this case there is overlapping and I need a code that avoiding this situation.
Thanks
I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.
Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display?
The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?
At the moment I am working a little project at work and am stuck on how to calculate departments due date based on actual order due date.
example:
OrderID: xxxxxx-xx DueDate: 10/8/13
Now let say i have 5 different departments but I want/need to know what should be the due date for each of those departments based on the due date on top.
I need a query that will relate values to given date ranges. For Example:
Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom
Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15
Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:
Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)
Not sure how to set this up the right way.
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?
I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.
Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
We use a device called a site master to test frequency levels across a system. This device stores plots onto graphs which are then downloaded onto an Access (95) database. When the plots are captured before they are put into the database they are time and date stamped among other things. When I open the Database in access I can go into the fields and physically change each of the dates. Yet when I use the site master software to open the database the Time and Date stamp appear at the bottom as the original values and not the changed values that I have assigned. Also when you right click the plots table and go to the properties. The created and modified dates have stayed as the originals. What I would like to be able to do is adjust this date. And what I would like to know is this possible??? Is it like a Windows File 'System' that is giving this date or is it a function in Access that can be changed?
View 4 Replies View RelatedI've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.
What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(
Help!
Here's a good one for the experts...
I have a query that sums downtime on equipment. The problem that I came across is that there can be multiple status items open up on the same piece of equipment. When the restored time on one status item occurs after the down time of another, the query still adds the total down time of both status items which is giving me greater downtime than there actually is.
Is there a workaround for this?
Thanx,
MJ
I work for a chemical company, over the course of the day there are 2 shifts, one from 07:00 - 19:00 and the other from 19:00 - 07:00. I want to measure the amount of time a piece of equipment has been offline for, and Sum these times for each shift, these pieces of equipment can go offline multiple times per day which is why I need the sum function. The fields I currently have are:
[Date1] : The date at which it went offline
[Time] : The time at which the piece of equipment went offline
[Duration] : The length of time it was offline for
For each day I wish to sum the offline time in two fields called:
[Shift 1]: The previous nights shift (between 7pm the day before and 7am that day).
[Shift 2]: The days shift (between 7am and 7pm that day).
Where to begin but the logic should be something like:
[Shift 1] = Sum of [Duration] For(([Date1]-1 and [Time] between 19:00 and 23:59) And ([Date1] and Time between 00:00 and 07:00))
[Shift 2] = Sum of [Duration] For([Date1] and [Time] between 07:00 and 19:00)
I currently have two tables storing all this data, one containing [Date1], and another containing the breakdown information such as [Duration], [Time] etc. They are linked by the [Date1] relationship. The reason for the first table only having the [Date1] field is because it is a Main Form which contains various sub-forms.
I am trying to build a query that depending on the value stored in a combo box called SHIFT, either lists all the entries that have been made by our dayshift (07:00-19:00) or our night shift (19:00-07:00)
Each of these components work if I seperate them out
IIf([forms]![FRMFRame74]![SHIFT]=1,Between #07:00:00# And #19:00:00#,Between #19:00:01# And #23:59:59# And Between #00:00:00# And #06:59:59#)
A function I can use to calculate the midpoint between two times? (i.e. starting point, plus the total hours divided by 2)
Examples:
11PM to 4AM = 1:30AM
4PM to 5PM = 4:30 PM
7PM to 9:30PM = 8:15PM
Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
How do i get the query to add up all of those quantities?