I've set up a query to calculate the difference between 2 time fields. I set the property to short time.
The thing is - when I run the query in a Select query, the times in the column which contains the difference, appear correctly : ie hh:mm, but when I run the same query in as a make table query, the time difference field (although I have set the property to short time) appear something like this:
0.664464564646
Can somebody please tell me how to make it that the time difference results field in my make table query appears as hh:mm??
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
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.
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1 WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated FROM [OTMissing] ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
I am trying to add hours entered in a short time format. For example 08:15 or 02:55. I also would like it to give a total number of hours beyond 23:00. Here is a table with a Name Column and Hours Column
A) write a query to add up the hours. I believe Heather would total 25:31 Hours.
B) Show this same total in a report? In my report I had a Name Footer which showed "=Sum([hours])". Will this work once the query is right or will I use the "=Sum([totaltime])"?
Also, is it possible for you to respond in a SQL statement so that I can copy? Thanks.
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 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?
This must have been answered before, but I can't find it by searching so sorry for the dumb question.
I input a time in hours and minutes in a field with a Short Time format. I would like then to convert this to seconds in a field with a number format so that I can total the seconds and then add them to the results of a calculated DateDiff control to get the grand total of time spent on a project.
I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.
The totals query allows users to view what products are shipped between a period specified by the user.
The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.
I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is
Code: Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]
Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.
Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have two fields that are in short time format, and are saving the information to the table in short time format.
I then have a field that I have set to auto-fill under the chosen circumstances, with a text roughly as follows
Code: Dim Story As String Dim IDepTime DimADepTime IDepTime = [InstructedDeparture] ADepTime = [ActualDeparture] Story = "This person was instructed to leave at " & IDepTime & " but did not do so, instead departing at " & ADepTime & "which caused us a problem" me.txtnarrative.value = Story End Sub
This all works, however the times being displayed are in hh:mm:ss format, when I only want hh:mm format - how do I tell it the format I want?
I have a database I built for my employers that is accessed by up to as many as 50 users at one time. It is a relational database. I did a compact and repair this morning which changed the BE from 215MB to 45MB. I went to lunch and came back and the database is now 58MB. Does anyone have any ideas as to why it has increased by 13MB within an hour? There is no way this is caused by geniune data records.
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
I have a table with many records each record has specific time. I would like to make query by time to see how many transaction has been done every 10 minutes.
I made a query its show me only one period. how to see all others period.
For example:
Start Time--End Time -- Amount (Total of amount for the period) 10:00:00 -- 10:10:00 -- 2000 10:10:01 --- 10:20:00 -- 1500
and for all other period :
I have attached the file with the data table and query.
In My Db I have TblSupportTickets and a Table which records support ticket activity. When adding a new activity detail to the Db it automatically records when the FrmActivityDetail opens and on closing, it records the time the Form was closed. So in TblSupportTickets which relates by ID to TblSupportTicketActivity on a one-to-many relationship.
I then created a query with SupportTicketID, TimeOpen, Time Closed. SupportTicketID criteria is Forms.FrmSupportTicket.txtSupportTicketID
This lists all the time open's and timeclosed's for that support ticket.
how can I work out the total time spent on that support ticket?
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
I have a query where one field is Date:Now() so that todays date is forced in. However, it is giving me the date and time. I only want the short date, how do I achieve this???
