Modules & VBA :: How To Modify Code To Accommodate Time That Exceed 1440 Minutes
Oct 8, 2014
I have a function that formats minutes to "hh:mm:ss" but its doesnt work if minutes is equal to or greater than 1440 minutes/24 hrs.
Code:
Forms![frmTasksTimer]!estimate_time = FormatTime(Forms![frmTasksTimer]!amount_left, Forms![frmTasksTimer]!Rate)
Code:
Function FormatTime(Amount As Double, Rate As Double) As String
Dim x As String
x = CStr(Round(Amount / Rate * 60))
FormatTime = Format(x / 1440, "hh:mm:ss")
End Function
How can i modify this code to accommodate time that exceed 1440 minutes?
View Replies
ADVERTISEMENT
May 15, 2015
I want to make delay, for example 2 minutes when the code is within the loop.
Example
Code:
For i = 0 To 100
Debug.Print i
'Before go to the next to make the delay
Next
View 12 Replies
View Related
Jun 27, 2014
My problem is simple, and i imagine it is easy to fix. I have more forms in my db than I would like to count, and I have a couple lines of code to add to the onload event of each form.
Is there an way, without actually having to edit each form myself, to add the code i need to add to all forms. Is there some code I can write that will let me modify the on load property of a form, and save it?
I know what i need to do, minus the part where i actually change the onload event.
View 5 Replies
View Related
Mar 12, 2014
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
[code]...
View 3 Replies
View Related
May 4, 2008
I want to be able to make code that will modify a data structure in another file. Can I do it in an access application or will I need to make a VB application? I want to add some more fields to a table. :confused:
View 1 Replies
View Related
Mar 1, 2007
Hello all,
This forum has been a lifesaver in the past and I hope someone can help me now.
I have a Contacts form that is used to input all the client's information. (name, date of birth, address etc).
I want to be able to force the person entering the data to not be allowed to tab to the 2nd text box (or any other text box on the page) unless in the first text box data has been entered (the first text box is client name)
However, I also need to be able to overide this if the person entering the data clicks on a "Cancel" button (another button on the page which closes the form).
Is this possible and if it is any help would be greatly appreciated!
Thank you in advance,
Rob
View 14 Replies
View Related
Apr 20, 2006
Hi,
If I have a time like 22:00 and I want to add lets say 300 minutes to it; so it now shows 03:00 how do I do this? I can do it in Excel alright with the TIME function but cannot find a similar feature in Access
Liam
View 1 Replies
View Related
Mar 31, 2008
Hope some one can help I have a field with a time value in it in the format:
08:30 (this is a date/time field) what I would like to do is convert this value in a query to minutes.
Ie.
08:30 (8 hours 30 minutes) convertes to 510 minutes. any pointers. Thanks.
View 1 Replies
View Related
Dec 9, 2004
Hello again,
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!
G
View 1 Replies
View Related
Jan 29, 2007
Hi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.
First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")
This is working until I have something like :
03:00
15:45
25:20
this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.
why would this be ?
View 4 Replies
View Related
Nov 2, 2004
Hi,
I have a bit of a problem. I am making a program in access 2002. In my program you should punch in hours:minutes, this is for keeping track of how long a job has taken to perform. The problem is that it could exceed 23:59, it could take up to a week or even more. In my database we have already put in a lot of info where it doesn't exceed the 23:59, I would like to keep this posts and just to put in new ones that I could use further on when I am about to take out reports, where I have to calculate time. Is there someone who knows what to do ??
Regards
Greger
View 6 Replies
View Related
Mar 30, 2006
Is there any way to take a field that is a whole number and convert that number to appear like a time?
i.e.
120 shows as 2:00
106 shows as 1:46
25 shows as 0:25
View 2 Replies
View Related
Jun 27, 2012
Is there a way to make a shared (split) database automatically log/time users out if they leave it open/idle for too long (ie: more than 30 minutes)?
View 1 Replies
View Related
Jun 26, 2015
The task is (1) output an Access query to Excel (2) overwrite that file if it already exists (3) apply specific formatting to the header row and the other rows in Excel. I have cobbled the code together from two sources. The beginning and end are adapted from code on btabdevelopment.com but the large insert in the middle is code I got form a project a former colleague had done. But he's no longer around.
The problem: I click the button and everything works OK. The file is created and formatted just how I want. If I click the button a second time though, it seems to run OK, but when I open the file it is NOT formatted. However, theres another window behind it called Book 1 which has all the data and all the correct formatting it just hasnt been saved. If I click it a third time I get an error message that says Object variable or With block variable not set. Im not even 100% all that is accurate because I have tried it a multitude of ways, closing and re-opening the form, closing and re-opening Access itself, starting with Excel open or closed, never with the destination excel file open though. I dont seem to get exactly the same behaviour any two times. But as far as I can see, if I close and re-open Access, it always works the first time. So I can live with it.
Code:
Private Sub cmdExport_Click()
On Error GoTo Errhandler
Dim rs As DAO.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
[code]....
View 8 Replies
View Related
Apr 28, 2005
My table, TblTYPE, will contain two field.
TYPE
DURATION
There will only be 7 records in this table:
FUS, 30 minutes
POS, 30 minutes
PRE, 30 minutes
NOV, 90 minutes
FUL, 90 minutes
ANN, 90 minutes
NPB, 60 minutes
1st question: How do I enter only minutes in the table for field 2?
------
I want the following to happen.
The end-user enters the start time and the type. As soon as the type is entered, field three calculates an end-time based on the start time and the type. When the type is entered, the minutes listed in field two of the type table are added to the start time (giving you an end time).
Can anyone help me?
1.) How to properly format the minutes in the original table.
2.) How to add the minutes to the original time in a query.
Thanks!
Scott
View 2 Replies
View Related
Feb 7, 2014
Basically, I get daily reports from the client and the AHT and ACW values come in seconds. I've recently added code to the query changing the data to nn:ss which works perfectly. Below is some info:
tblTelephony
[AgentName]
[Calls] (number of calls answered)
[AHT] (in seconds)
[ACW] (in seconds)
[Code] ....
The above code works very well, where I'm stuck is in the attempt to average the fields [AHTMinSec] & [ACWMinSec] by Agent in a report.
Using [AHTMinSec] as an example:
I have the daily values for each agent [AHTMinSec] in the detail, Avg([AHTMinSec]) in the Agent group footer showing each agent's average, Avg([AHTMinSec]) in the Team group footer showing team averages and Avg([AHTMinSec]) in the report footer showing the campaign average including all teams. When I run the report the details are hidden providing each agent's average, the team average, and the campaign average.
The report worked just fine until I converted to minutes with the above code. Is there a reason that I'm getting an error stating that the calculation is too complex? I've done enough research to determine that the db can't Sum in order to average...
View 12 Replies
View Related
Jul 2, 2014
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
View 8 Replies
View Related
Nov 5, 2014
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.
Code:
dim msg as string
msg = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbCrLf & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & vbCrLf & _
" <soap:Body>" & vbCrLf & _
" <UpdateDataForScreens xmlns=""http://mdtoolboxrx.com/"">" & vbCrLf & _
" <PatientObj>" & vbCrLf & _
[code]....
View 10 Replies
View Related
Jun 12, 2013
Basically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
Table
- Start Time
- End Time
Query
Difference Time: DateDiff("n",[Start Time],[End Time])
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
View 4 Replies
View Related
Dec 11, 2013
I want to modify a table called FC_TEMP via adding some columns from another table called AVERAGE_TRP. Do I have to create a new query table and then insert the output into a new table?The following code shows me : Cannot execute a selected query.Is there a way to update this FC_TEMP table without creating another temporary table?
Code:
Public Sub Update()
Dim strSQL As String
strSQL = "SELECT FC_TEMP.*,AVERAGE_TRP.[Average_new_TRP_EUR],AVERAGE_TRP.[Average_old_TRP_EUR],AVERAGE_TRP.[Average_Margin] " & _
" FROM FC_TEMP " & _
" INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID]"
[code]...
View 5 Replies
View Related
Jan 22, 2008
How can I convert hours and minutes to minutes as an Integer?
For example, convert 2:15 to 135.
Any help is greatly appreciated.
View 8 Replies
View Related
Jun 28, 2013
I have the following code for hours worked by staff over a 24hr period. However, I want to calculate staff pay by using their hours worked. The table I am using is populated with the minutes i.e. 270 but I want this field to display 3.5 hrs. I have initially got round this by using a calculated field in the table [Hoursworked]/60*[StaffPay] but this is not an ideal fix. Can I add something to the code below to display the hours to 2 decimal places.
Code
Dim ilHoursWorked
ilHoursWorked = IIf(CDate(Me.txtStartTime) < CDate(Me.txtFinishTime), DateDiff("n", CDate(Me.txtStartTime), CDate(Me.txtFinishTime)), 1440 - DateDiff("n", CDate(Me.txtFinishTime), CDate(Me.txtStartTime)))
View 2 Replies
View Related
Oct 20, 2012
I built an Access Database (at work in Access 2007) that has over a thousand records. It contains two subforms. I constructed it as follows:Built my MainForm (frmMain) to record report details (like serial number, title, date, time, etc.)This data goes to MainTable (tblMain) and there's only one record per reportBuilt and inserted SubFormNames (frmNames) to record names in report.This contains multiple records {has its own table (tblNames)} and is linked to tblMain/frmMain by serial number
Built and inserted SubFormCharacteristics (frmCharacteristics) to record events and characteristics using one text box for narrative (e.g. suspect robbed bank on 12th and Second Street), multiple yes/no check boxes (e.g. Perpetrator armed?, Multiple perpetrators, etc....), and several multivalued fields.This contains multiple records {has its own table (tblCharacteristics)} and is linked to tblMain/frmMain by serial number
I've reached the limit of 255 fields in the tblCharacteristics and I wish to add more characteristics.I created tblCharacteristics2 and tried adding those fields to tblCharacteristics but it doesn't work.I really don't want to create a frmCharacteristics 2 because all of my characteristics are alphabetized in frmCharacteristics and I would have to jump back and forth between the two subforms to enter my work. how I could link the checkboxes in tblCharacteristics2 to the narrative in tblCharacteristics (or tblCharacteristics).
View 3 Replies
View Related
Nov 8, 2013
Is there any way to modify the progress bar so instead of saying "running query" it states the name of the query?
Like: Running Query [qryFRED] <progress bar>
View 1 Replies
View Related
Mar 12, 2014
If the database sits dormant or unused for 10 minutes the login window needs to reappear for a possible new user.
View 1 Replies
View Related
May 19, 2015
I have to write piece of code to achieve the following:
1. When the Form gets opened then timer will start.
2. It will fire BtnClose_click event automatically after 10 minutes if there is no activity for 10 minutes.
3. Whenever BtnSave is clicked by the user then reset the timer.
View 2 Replies
View Related
