I have the following SQL to add hours
SELECT tblTrip.PaySlipReference, Sum(tblTrip.NDays) AS TotNDays, Int(Sum(tblTrip.FlyingTime)*24)&"."& Format (Sum(tblTrip.FlyingTime),"nn") AS TotFlyingTime, Int(Sum(tblTrip.DutyTime))&"."& Format(Sum(tblTrip.FlyingTime),"nn") AS TotDutyTime, Int(Sum(tblTrip.TAFB))&"."& Format(Sum(tblTrip.TAFB),"nn") AS TotTAFB, Sum(tblTrip!DutyPayRate*tblTrip!TAFB) AS TotDutyPay, Count(tblTrip.NDays) AS NumberOfTrips
FROM tblTrip
GROUP BY tblTrip.PaySlipReference;
Refering to the part "AS TotFlyingTime" it works perfectly as the format is a 24h and never goes over 23.59 hh.mm. The problem I incour is a incorrect result for the other two parts of the code: "AS TotDutyTime" and "ASTotTAFB", these data have values over 23h59m.
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh] data eg (this is how I would like it to be inputted into the table) 7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
I'm creating an attendance tracking system, the attendance system needs to : Allow an instructor to enroll students into one or more classes, and then add attendance by date to each class(es). The problem that I'm having is, attendance is tracked by the number of hours that a student attends class. For instance, if class1 is in session from 8a.m. - 12p.m. student1 may only stay 2.50 hours of that class and student2 may stay 3 hours. The teacher needs to be able to add attendance to class1 for July 12, 2013 and add hours for each student enrolled in this class session who attended on that day. I have the following so far, minus the attendance:
Students -This is just the basic demographic information that instructors will need to enter on students. StudentID (PK) StudentLastName StudentFirstName DateOfEnrollment LevelOfStudy DateOfBirth
Location - These are the locations in which our students can attend classes LocationID (PK) LocationName County City LocationPhoneNumber LocationFaxNumber
Classes - These are the classes that our students can attend. CRNNo (PK) ClassName LocationID InstructorID
Instructor - These are the instructors that teach our classes. InstructorID (PK) InstructorLastName InstructorFirstName InstructorEmailAddress
Enrollment - This table enrolls the student into a class StudentID(PK) CRNNo (PK) LocationID (PK)
Hi guys, Excuse my novice questions. I have a query in which i did two things: defined a Column "Line Item Cost " as Sum ( using the dropdown in Design Grid of the query). Then i calculated another field: Expr13 which is: Expr 13: Sum([Line Item Cost]+ [Change Request Total Cost])
So, If Line Item Cost = 1363000 and Change Request Total Cost = -40000, then Expr13 : 1363000-40000 = 1323000, but i am getting the values: 1283000(Somehow the value is getting doubled before addition)
Please look at the attached Document. Please Help!
Hoping you can help me out with a problem I have... Basically every now and again I get a call from my client saying they cant add a new record, to any table, they get a message saying they are not allowed. I go in and it seems the DB has somehow locked itself and I have to open it up and repair it... it is a multi user setup with the database on a server.... I have seen the users actually end task on Word if it doesnt respond quick enough and was wondering if this would cause the locking problem!? I am just asking to see if there is a setting or something I can do to try and prevent this happening!?
I get an error message when I add a new record to my database. It occurs when I open my form and use the [arrowright*]-button and when I use the switchboard button to add a new record using a form.
An error message is displayed, but when I dismiss the error (click OK), then everything seems to be normal and the record is added normally with the entered info. It is a bit annoying, though.
Since I have the dutch version, I'll try to translate the message: "You can not add/append a value to this object
* The object possibly is a control-element in a read-only form * The object is part of a form that is opened in the design view * The value is too large for this field"
The error occurs right after I have typed the first character into a field on the form. The form consists of a few subforms. And I don't think any of the by access suggested errors have anything to do with this...
Any help is appreciated, Thanks in advance, MuFfiNimal
I am a neophyte Access 2003 user attempting to build a new Form (all about Lynyrd Skynyrd but that is for discussion in a different forum)
When I attempt to add a Command Button to a Form in Design view, I get stepped through the wizard and all seems to be going well to enable me to add an “Add a New Record” button.
But at the end I get an error message that says “Application Defined or Object Defined Error.” What do I do next?
Thanks for your help, Southern Rock is depending on it.
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code: Shell ("Outlook.exe") Dim outMail As Object Set outMail = Outlook.CreateItem(olAppointmentItem) outMail.Recipients.Add (Me.txtsupervisor)
I want to add a record with attachment to a table in an access (2013) database.
If I ignore the attachment error everything works as it should, but as soon as I add the code for the attachment I receive error code 3259.
Code:
Private Sub add_tune() Dim dbtune As DAO.Database Dim rst_tune As DAO.Recordset Dim rst_tune_in As DAO.Recordset Dim rs_child As DAO.Recordset Set dbtune = CurrentDb
[code]....
Since I have a dutch office version the error text (ongeldig veld gegevenstype) is in dutch, translated it means something like a data type mismatch.
t_first_bars is a field in the table tbl_tune . The field type is attachment.
I have a main form with several subforms in tabs. From one of the subforms I list linked records to the main form (Clients) within that subform (Bank account details). I list the records and have a button to add new records.
Where the Client has one or more records in the subform the add button works perfectly.
When the subform has no records the add new records button produces the following error "Run-time error '2498' An expression you entered is the wrong data type for one of the arguments"
The add button has
Private Sub Command52_Click() DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId End Sub
the "AddClientBankDetailsFrm" popup form has
Private Sub Form_Load() If IsNull(Me.OpenArgs) = False Then MsgBox "Form was opened with ClientID = " & Me.OpenArgs Me!ClientId = Me.OpenArgs Else MsgBox "No ClientID was passed." End If End Sub
ClientId is the primary key of the main form and the secondary key in the new record.
error message I am getting when I click on my Duplicate Record button (created through the wizard).
I have two combo boxes on the main form that populates data when the user makes a selection from the combo box. First combo box populates project data and the second combo box populates equipment data. The form is working well with the two combo boxes populating the data into the main table.
Now I would like to add a duplicate record button to copy a record and paste the data as a new record. So, I added a duplicate record button using the wizard and I am receiving the following AfterUpdate error.
Run-time error 3020: Update or CancelUpdate without AddNew or Edit.
This is the code I am using to copy and paste a duplicate record:
Private Sub InputForm_DupRec_Button_Click() On Error GoTo Err_InputForm_DupRec_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdSaveRecord DoCmd.RunCommand acCmdCopy DoCmd.RunCommand acCmdPasteAppend Exit_InputForm_DupRec_Click: Exit Sub Err_InputForm_DupRec_Click: MsgBox Err.Description Resume Exit_InputForm_DupRec_Click
It seems to be duplicating the data from the first combo box, but not the second one where it errors out.
I'm trying to add a combo box field to an existing table and form. I added the new field to my existing table and set it up as an combo box added my row sources. Then added the new field to an existing form and now am getting a error stating "The record source "Table name and added field here" specified on this form or report does not exist. When I just make the new field a text box instead of combo box it works just fine.
I am using the now() function in my date_added field. My site is GMT based but my server is in the US and is returning the date added as 6 hours before which does not look good on the site ...
Is there a way to add 6 hours when the timestamp is being added ?( now() +6hrs ) for example...
Or can I add 6 hours to a time stamp on read back? If so how can I add this in clasic asp?
Hi, I am trying to figure out a way to keep record of store hours, and days open for lots of different restaurants. The only thing I could up with is the following. I'm not sure how to keep track of store hours, or even the best way to make a table for hours. Any help is appreciated, thanks.
I've searched and searched to no avail to find the answer, hopefully you can help.
The story is:
At work, our holidays are now taken as hours off. For example...if I take a Monday off, its 8hrs and 5 min and a Monday morning would be 4hrs and 5 min...What I want to do is calculate how many hours I have taken off throughout the year.
The way I was going to do it was:
Create Table called Times,
Fields = Week, Day, Morning, Afternoon, Friday and 1hour all Data Type "Date/Time"
Similar field names Wk, Days, Morn etc. They would be Data Type "Number"
Now in my simple head, I thought I could simply record how many weeks or days off i had off. Then create a query (for example) Week*Wk to calculate the amount of hours I had off. In simple terms if I had 2 weeks and 2 days off, the query would multiply Week (37) by Wk (2) and Day (8:05) by Days (2), giving a total of 90 hrs and 10 min.
I know there is a datatype mismatch, but is there a way round this.
I have a query in which I need to display just the data that was entered from 9:00 p.m. the previous day until now. There is a date/time field called "QtyTime".
I have an access table, and there is a field called user_time. Once a user record has been inserted, it puts a timestamp on this field, to record the date/time the record was saved to the database.
Now I need to write a query to check if it has been 72 hours since the insertion of the user into the database. This will exclude weekends. If a record was inserted on a Friday, on Monday it will not be 72 hours, as weekends are not taken into account only weekdays. So Monday will only be 24 hours since the user was added.
Hi, I have been looking for some answear, but haven't find the proper one!! I have a form containing a number of records showing the hours worked in format HH.MM at the end of the form I have the sum of the hours, but the result is not correct. To make it simple to understand, actualy the total of hous for 11.55 and 11.14 gives me the result of 22.69 but the correct result should be 23.09 (23h and 09m and not 22h and 59m) How can I get the right total??? Marco
Looking to have a time field that a user can enter how long they worked an issue. Format should be ##:##, but I'm afraid that if I use text, calculations will be impossible. How does someone do this if it's expected that the value will frequently go over 24 hours?
Hello. I have this continous forms which gets data from a query. The query has a field that sums date(hours), and I want them to show more them 24, for example, 42:30.
If I use general number it shows something like 1.2303256 and if I use short date it show something like 02:00 for 26 hours.
If I use a simple code is shows the SAME value in ALL the continuos form.
Which code should I put at the forms field so it would work, that is, it shows a diferent short date in ALL the continous form?
