Tables :: Phone Statistics - Formatting Field To Show Duration Not Time
Apr 13, 2014
I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.
My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss?
what is the best way to record durations, not time?I want to have a table that records charging data such as below:
startDate
startTime Duration (hh:mm:ss)
15/04/2012 12:00:00 13:00:00
In this example you can see we started charging the device at 12pm on 15/04/2012 for 13 hours, from this I will be working out the endDate/time using dateAdd etc.Do I set the duration field up as a dateTime and if so how do I avoid the DB thinking that the field is a time and not a duration, meaning that the endDate would be 1pm on the 15/04/2012 instead of the correct 1am on the 16/04/2012?
I have a SSN field and i want it to have the dashed (I got this part down.) But when I click on the field to copy an paste it I want the dashes to disappear. I am thinking What ever the code (if any) would on in the fields onFocus event. I don't want it to copy the dashes.
In a table, I would like a particular field to look thusly:
xx-xx-x-x-x
My question is how do I format it so that the dashes (-) are automatically inserted when the data is keyed into the input screen. The dashes would also have to be displayed on the table.
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM [PunchOut] = 11/23/2013 5:43:30 PM
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
I have been using Access for several years but always in a basic way. I have started work on an existing Members database where the Members Addresses have been pasted in from Notepad or been imported from csv or manual entry.The ones that have been manually entered are on one line. The ones that are imported from csv or pasted from Notepad are on multiple lines like you would naturally type on an envelope..In Notepad they look like this:
'Oakdale' 123 Expression Road Name of Town Name of County Postcode
Sometimes the addresses have 4 lines and sometime they also have the name of a village which makes it 5 or 6 lines. When imported into Access they also view (datasheet view) in the same way with the return at the end of each line. However, I cannot enter a new address in this manner directly into Access.How can I enter the address text into a text filed and be able to format it so that it looks like the above?
I have searched many times for an answer to this but cannot find a way of explaining it that search engines understand. I first came across this 12 years ago when I worked on a massive Members database for a radio station all the addresses where formatted in the same way and had been imported through csv. When I do this the csv file marks up the text as "'Oakdale' 123 Expression Road□Name of Town□Name of County□Postcode"
I have tried exporting to csv ad then importing it into GMAIL and this works fine I have also printed off labels and envelopes and all the addresses print fine too; I just don't know how to input it in that format other than pasting all addresses through a basic text file.
In Access 2002, I have a Phone Number field (Text) that is meant to store (obviously enough) phone numbers. However, when I enter 2009 as the last four digits (ex. (555)-555-2009) the number is changed to (555)-555-2010. This also occurs if I enter the number directly into the table (which doesn't have an input mask). I have tested it in a new form (even without input mask), and a new table, which both result in the exact same thing.
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
One table with Student Basic Records with Course name and Total Fee
Tabel name Student_Rec and Fields are below ID, StudentName, Course Name, RollNo, Total Fee
then another table name Fee_Details for receiving dues in installments. ID, RollNo, FeeDue, FeePaid, Comments
Now i want the FeeDue Field should show the Balance feedue of each student
I mean it automatically check total fee due from student_Rec table and also check all previous entries for the same rollnumber in Fee_details Table and show the pending amount as FeeDue.
I'm setting up a query which will eventually run as a report to show sales within a specific date period and so on.
In my table "tblJobs" I have a field named "JobInputDate" - this will act as my date source, everything else included within the query works fine, i.e. costs etc, etc.
The format of the date field is Short Date and the default value is the date the entry was made to the table, this obviously uses the =Date() expressing to generate the date.
Now, when I come to building my query I want the dates to be shown as a day of the week, so that when I eventually build my report, I can group them by day.
I've looked through the various threads on here which all point to using the Format([YourDateField],"dddd") format. However when I run the query I get the "Data mismatch" error.
We are setting up a table and are trying to get a field to be a "time" field. I've changed the field to "date/time" and have chosen 'short time' in the background but when I open the table it keeps giving me a calendar to choose from then turns it into a time.
After conditional formatting with every new entry in Datasheet Item Code or Item Description then after row Disc % and Amount default values is shown as #Type! And datasheet is very slow with flickering. If it conditional formatting is remove then working as well. I have attached the Screenshots and dB file ...
Apologies if this has been asked before but I have a problem with being able to format a field to accept an hours and minutes value where the hours is likely to be greater than 23.
I have a form that is related to the number of hours that people spend in respite care. Entry and exit dates are provided in a standard way - date and time - and I have a calculating field that calculates the number of hours and minutes that a person spends at the respite centre based on the entries made in the entry and exit fields.
Because the calculating field is based on a module and I need to have the calculated amount included in the table, I have set up a VBA code to copy the result of the calculation into a controlled field elsewhere in the form.
However, I also need the form to do a simple calculation whereby hours spent away from the centre are subtracted from the total hours calculated by the module, so I need the copied value to be in a format where the calcualtion is able to be performed.
In a nutshell I guess I am looking for a time format for the field that will allow the recipt of a value greater than 23 hours.
Of course perhaps I need a more sophisticated VBA script to perform the calculation behind the scenes but I admit to not being particularly at ease with VBA scripting. If anybody has any suggestions about either a format or a suggestion for a VBA script, I would be very appreciative.
I have a time/date field. The format for the field is short time hh:mm I created a Private Sub for when the field is double clicked the time is populated.On Double Click:
Private Sub Time_of_Call__DblClick(Cancel As Integer) Me.[Time of Call:] = Time() End Sub
It works great. I double click the field and "poof" the time appears. My problem is when the time appears it is in hh:mm:ss..I need it to be in 24 hour/ military time.
I have form with a label and input textbox and a cmmdbutton.In that text box you can fill a number and that number is the scale in minutes.When i click on the cmmdbutton, a table or query shows up with the time and its scale.For example if i put in the form, the number 10 in the textbox and click on the button, A table must show up with the values 00:00, 00:10, 00:20 ,00:30,00:40,00:50,01:00, etc till 23:50.
I am having some difficulties with a Date/Time Field. I am importing a | delimted text file into a table and the Date Field is resulting in a Type Conversion Error.In the raw text file, the Date Field has the following Format (example): 01/03/2013 03:11 PM
My import Spec is as follows: File Format: Delimited Field Delimter: | Language: English
[code]....
The only thing I can think of, is that the mix of Leading Zeros in the Time AND AM/PM is causing a problem. But, I do not see a way to address this with an import spec.The odd thing is that if I import the DateOpened Field as Text, THEN change the DataType to Date/Time AFTER import, then save the table, it recognized/converts the DateOpened Fields correctly.I'd LIKE to get the import spec correct (I have to update twice daily), But, barring that, if I could import as Text then build a Macro that would:
1) import text file(s) 2) change certain fields datatypes to Date/Time 3) Save Table(s)
That would suffice. I could then use VBS (and perhaps windows scheduler) to run the macro when needed.
All, using access 2010. How do I display the default value of a date/time field to just the current year instead of using =date() to get the full date.
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box. - The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
I have a table in my Access 2010 Database with few date/time fields. I have set different formats like Short Time, Medium Time etc. But when I link this table to Sharepoint list, all the date/time fields have been modified to their default format (General Date).
Is there a way we can keep the formats as such even after linked to Sharepoint list? If not, is there a workaround?
