Hi,
I need to calculate the number of days between two days however the format on which i have my date includes the time in the same field -
2007-02-20 07:15:30.474
When I calculate one date minus an other the result is #error
I require the result to be in a count format e.g 2.3 days (like in excel)
Is there anyway to get around this without splitting the date and time?
Please help!:)
Calculating the rates over a period of time. I think the attachment will give you a clear picture of what I am looking for.
To get the cost for a particular period I want the rates to be updated as per the given period. How I could get the calculation in the mdb format...
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
Hello friends,
I have a table for "Production Orders" ie. work orders.
there are feilds : start date , start time
based on these feilds i want to calculate the remaining time to start production.
but what i want is
if remaining time is > 1 day then
give results in the format " 1 day and 2 hrs"
elseif remaining time < 1 day then
give result in the format " 2 hrs and 24 mins"
and also current time - remaining time > 0 else result = "N/A"
how can i do this simply
I'm having trouble getting my head around this.
I have a database that collects stats of patients admitted to ICU.
I have a table PtDataTable (pk = PtID) with a one to many relationship with PtVisitTable (pk = VisitID, fk = PtID). The patient visit table has the fields AdmissionDate and AdmissionTime, and DischargeDate and DischargeTime.
I need to calculate if the patient is readmitted to ICU in less than or equal to 48hrs. In other words calculate the time elapsed between DischargedDate and DischargedTime in one record and the AdmissionDate and AdmissionTime for other records in the PtVisitTable for the same patient.
I'm not sure if I stated this clearly.
Any help is appreciated as always.
Table
------
I have one field called [Time] in dd-mm-yyyy hh:mm format in table A.
I have another field called [Deadline] in text format (Mon 10:00) in table B.
Queries
-------
Need to build one queries that have the above two field, but with additional field called [PullDeadline] that the [Time] will look out the [Deadline] and return the actual time & date value in [PullDeadline]. No matter how the [Time] change, the [PullDeadline] will stick to [Deadline] and return the value in that particular week.
For example,
[Time] = 01-12-2007 05:00:00 AM
[Deadline] = Mon 10:00
[PullDeadline] = 26-11-2007 10:00 AM
How to handle, if the deadline is previous or next week?
Eg. [Deadline] = Sun 10:00
Then [PullDeadline] = 25-11-2007 10:00 AM instead of 02-12-2007 10:00 AM.
OK so I have a form with the following fields which are all date/time type
apptTime
timerOn
pickupDate
reminder - combo box with 1 hour, 2 hour...etc up to 10 hours
so what happens is when user enters apptTime say 18:30 with pickupDate set for current days date 10/14/2006 then chooses 2 hours from the reminder combo. This then sets timerOn to (apptTime-2 hours) so it is 16:30. I have set a conditional formatting to change background color when "timerOn<=Time() and pickupDate=Date()" so user knows to dispatch this load. This all works great until you get to midnight.
If user sets apptTime to 00:30 with pickupDate to 10/15/2006 and chooses 2 hour reminder, so timerOn does get set to 22:30 and you would expect the conditional formatting to work once the current Time() is 22:30, but since the current Date() is 10/14/2006, the expression is false. I have tried the DateAdd such as DateAdd("d", 1, Date()) command in various ways but can't get it to work for "timerOn<=Time() and pickupDate=DateAdd("d", 1, Date())"...I mean it works because it adds 1 day to pickupDate and condition is then true, but it ignores the timerOn<=Time() part and formats even if I reset system time to 22:29. Can anybody give me insite on where the logic is wrong?
I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:
datetime2 - datetime1.
I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!
Hi all,
i've a field that contains the systemdate n time
i.e
txtDate.value = Now
will give me this 11/30/1899 9:46:00 AM.
How do i get rid of the second hands??
i want 11/30/1899 9:46 AM instead.
Thank you
I wish to insert in a table, not the date, but just the month and year (yy/mm or mm/yy), for example 05/10 or 10/05 for October 2005 (the order of yy and mm does not matter). In addition, I need to be able to sort the records chronologically using the same field.
I have tried various solutions with the date/time field or the number field (e.g. using yy.mm where the mm are the decimals). However, it does not seem to work correctly. Any suggestions for how I can do this by configuring the format, inputs mask and validation rule?
Niels
Hi
I'm very new to Access, so please be gentle;)
I have imported a appointment database that I need to analyse, however the Date & Time data within the table has been written as;
Date: 20051220 - which I would like to read dd/mm/yyyy (20/12/2005)
Time: 800 - which I would like to read as hh:mm (08:00)
I would like to produce a report of all the queries, i.e. longest appointment, shortest etc... but display them in the correct Date/Time format.
So how should I go about this?
I cannot alter the imported table in anyway.
1. Can I produce a mirror like table of the existing table, that automatically use the correct Date/Time format?
2. Can I alter the values within a query?
3. Can I alter the final output in the Report?
What is the best way to tackle this?
Hey guys, been away from Access and SQL for some time. I have a simple 2 column table with one thats set to Date/Time that displays the date only. Unfortunaly, when I run my query, the date comes out 7/9/2005 and I want it to come out as 7/09/2005. I know I am going to have to force it out with a format command, but its been so long that I have no idea how to. Right now my query, in SQL format, looks like this.
SELECT * FROM Table1 ORDER BY Date DESC
I just need the format command to force the date. If anyone can help, thx.
Hi everyone
I have 2 date fields date1 and date2 , and i have date/time data type but what i want to be inputed is in dd/mm/yyyy and not mm/dd/yyyy, what do i need to do in accees both date fields properties to make sure it is in UK date format. thanx
I have played with the format and input mask but i am lost dont know what to do.
Thanx
I am struggling to change the format of 2013-07-29 11:33:03.6160000 to show dd/mm/yyyy
View 4 Replies View RelatedHi all,
I have an inherited database with years worth of julian dates stored as numbers (e.g. days 1-366 for a leap year). I also have some fields stored as short dates. I would like to run a series of parameterized queries on this data, some using the short data and some using the "julian dates" that are actually just numbers. I have the user enter the start and end date in short date format and would like that to be converted and held in an unbound field as a number value. So far I can get the date to appear as a "julian date" (e.g. 1/1/08 appears as 1, obviously the underlying data is still 1/1/08) How do I take that 1 and convert it to number value "1"? Thanks in advance.
Cheers,
Peter
I am working on a report in Access2000 which is getting information from several tables via a number of queries.
I am using nested IIf() functions to combine text formatted data in a calculated query field before using it in a report.
One such IIf() function is
Desc_col: IIf([HOVR - UDP].[SERVICE] Is Not Null,[DA] & " " & [SEQ] & " " & [TYPE] & [MO] & " " & [SUB] & " - " & [HOVR - UDP].[SERVICE],
IIf([MinOfCCT] Is Not Null,"I/O DIST CAB " & [UDP - JC CIRCUITS-05-F].[PWR] & " - CCTS " & [MinOfCCT] & " thru " & [MaxOfCCT],IIf([DESCRIPTIO] Is Not Null,[DESCRIPTIO],"Spare")))
When I have tried to do the same thing with date fields it does not work. The function is fine as long as it is not nested.
That is
Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],"")
and
Rev_col: IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],"")
both work.
But when I combine them into a nested function
Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],""))
I get an error message "! Data type mismatch in criterial expression"
There is no data mismatch in the original tables from which the information was extracted. The dates are all in DATE/TIME and set to "General"format in the tables.
I am wondering if I will have to use Visual Basic function to combine these fields. However that poses a problem. When I've looked at this I find that runtime VB is no longer available in Access and I do not want to go out and buy it on the offchance that it will work.
Can anyone tell me if there is another solution to my problem?
I have set a field on my FORM with the General Date format. Every time I use the date picker it inserts the correct date with a time of 00:00? I even changed the format on my table to general date - still no luck.
View 4 Replies View RelatedI am having difficulties changing a date/time field like '22/04/2013 09:14' to make it look like '2013-04-22 09:14' using a query.
View 3 Replies View RelatedI need to alter filed 24-gen-64, in 24/01/64... how to?
View 5 Replies View RelatedI want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours
I have a series of fairly large csv files which I need to consolidate by importing them to a single Access database. There are of the order of 50-60,000 'lines' per csv and about a dozen different files (all comma de-limited and text-qualified with inverted commas).The problem I have is, there are several date fields within the csv which are not formatted to Access' liking. Namely <Mmm_dd_yyyy_hh:ssAM> with underscores used here to represent spaces.
So for example, today's date appears thus :
Feb 6 2013 12:00AM (i.e. two spaces between 'Feb' and '6')
And this day next week would appear thus :
Feb 13 2013 12:00AM (i.e. one space between 'Feb' and '13')
I will obviously need this field recognised as Date/Time so I can query appropriately but I have no control over the source data and need to find a way of converting the format.One option would be to run a script on the csv's themselves prior to importing but I'm not sure how to do that.import this field as Text and then edit the contents after import (VBA?), such that the field can be redesignated with a data type of Date/Time.
I have a problem converting text to a real date value so I can do some calculations. I have a query that brings in data from an external data source. It appears the data is stored in the external table in text format and looks like this:
20050902 15:40:41
I have tried CDate to convert the text to a date/time format, but no luck. Any ideas?
I have a simple and small database having only one table. I want the data table to be exported in a spreadsheet format automatically at the end of the month. Is there any code or function to do that?
View 1 Replies View RelatedI have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
I want to be able to set text boxes so that if one enters a date and a time it displays in the format "dd/mm/yyyy hh:mm", but if one just enters a date is displays in the format "dd/mm/yyyy". Is this possible?
Stipulating "dd/mm/yyyy hh:mm" means that when you just enter a date it adds "0" values for the time e.g. entering "21/6/13" gives "21/06/2013 00:00".
General Date allows for an optional time, but it means that when you do enter a time it gives you seconds as well "dd/mm/yyyy hh:mm:ss" - and I don't want that.