Modules & VBA :: Time Calculations - Inconsistent Results?
Jul 9, 2014
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
Code:SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContributionFROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) AND RawData.ReleasePeriod=ReleaseMonth);
I am now try to set that query as the record source in a sub form using VBA but it throws a syntax error, run time error ‘3075’
Code:strRowSource = "SELECT RawData.OutlineNumber, RawData.OutlineDescription, RawData.DeliverableDesc, IIf(subQuery1.TotalProgress=0,"",IIf(subQuery2.TotalDaysForDeliverable=0,"",FormatPercent((RawData.PercentComplete/subQuery1.TotalProgress)*(RawData.Finish-RawData.Start)/(subQuery2.TotalDaysForDeliverable)))) AS WeightedContribution " & _ "FROM RawData, [SELECT DeliverableDesc, SUM(PercentComplete) AS TotalProgress FROM RawData GROUP BY DeliverableDesc]. AS subQuery1, [SELECT DeliverableDesc, SUM(RawData.Finish-RawData.Start) AS TotalDaysForDeliverable FROM RawData GROUP BY DeliverableDesc]. AS subQuery2 " & _ "WHERE ((RawData.DeliverableDesc)=(subQuery1.DeliverableD esc) And (RawData.DeliverableDesc)=(subQuery2.DeliverableDe sc) And RawData.ReleasePeriod = '" & Me.Combo10.value & "' )" Me.DeliverableStatus_Contribution_Of_Tasks_subform .Form.RecordSource = strRowSource
How is it that the query works when I run it but when I try to apply it to a sub form it gives a syntax error, any ideas how to correct this
I have a simple select query on a SQL table from Access. The query is:
SELECT tbl_Orders.OrderID, tbl_Orders.Approved FROM tbl_Orders WHERE (((tbl_Orders.Approved)=0) AND ((tbl_Orders.Completed)<>0)) ORDER BY tbl_Orders.OrderID;
The strange thing is that sometimes it pulls 34 results, and sometimes 38. From what I can tell, it should be pulling all 38.
What can I do to make sure it gets all the records?
I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
I have a small school with number of teachers, every teacher has a specific number of observations in the semester, i need a small project that when i query a bout a teacher, a form appearing with his name and the remaining hours of observation
what i did is i made two tables
teacher (id, name, total number of observations in semester) observation( Oid, name of teacher, date and time of observation, remaining observations)
I dont know if i have to make a counter or what !!-- Now what i need to do is a form when i enter the id of the teacher, it shows automatically the name of the teacher, and the number of remaining observations
How can i join the two tables and show the results ?
The fields include both date and time (date and time are in the same field) i.e. “6/26/2006 4:23pm”. The next field I need to calculate the duration, basically one field minus another…anything I’ve tried will only calculate the time difference will not factor in the days. For example, if it has been 52 hours (2 days, 4 hours) it will only say 4 hours. A few of what I’ve already tried are below. And I’ve also tried an elapsed time expression.
Im not sure if this is best place to put this but...
I have a table where date and time is stored in one field. It is stored in the following format:
dd/mm/yyyy dd:hh:mm:ss
The way the table works is that the date & time gets associated to a status code in the system. So for example a case (lets say ID is 12345) in the system may have a status assigned of ENQ with a date of 01/01/2008 00:00:00:00 and then another record for the same case (12345) where the status is WIP with a date of 15/02/2008 00:00:00:00.
What I need to do is do is a time taken calculation between the 2 dates and times but I am not sure of the best way to do it, if at all.
I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.
Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display? The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?
I have a query with 2 time fields, a start time, and an end time. I am subtracting the endtime from the starttime and I am getting funny numbers. Example: 12:12:04pm - 12:05:20pm and I am getting #Error. Where am I going wrong? Your help is greatly appreciated.:eek:
My boss wants me to program in visual basic, instead of using queries, a calculation between times, which values are gotten from a table. How do I perform a calculation between two fields from a table in code and what functions are there to calculate the amount of difference in minutes between times in visual basic? THANK YOU SO MUCH IN ADVANCE!
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error; and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
Ok i know negative numbers can be a bugger in calculations:
Code: Function FEV1pred(height As Double, ClientID As Double) As Double
Dim a As Double Dim Age As Integer Dim DOB As Date Dim TestDate As Date Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd")) 'In this case the clients Age is 39 a = -0.0244
'Now for the fun stuff...
FEV1pred = a 'returns -0.0244 so i know the dim a holds the neg number FEV1pred = (a * Age) 'returns 1.708 (which is incorrect, how this calculation is reached.) FEV1pred = (a * 39) 'returns -0.9516 (which is correct) End Function
So something about the dimming of Age seems to be doing something to the equation but i can't work out what?
I have partially done it using PrevRecVal module I found on the web.
I created what I need to do in Access in Excel first, the problem doing this in Access (for me) is I need to refer to the results in the previous record, PrevRecVal worked but I need to finish if possible.
The main report holds client policy data of which InvestAmount is used to start the calculation of the sub report, the data entered to run the calculations on the subreport would be
I dont get any errors with this code. Let me explain what im doing: T1 = temparary table (not really temparary but the values just change so often it might as well be) this table has field names: partname, order, maxorder, etc. stuff like that pretty much its a table that has records for each part name.
T2 = main database this table holds the order for all parts for a particular time and day
T1 is the one that people are familiar with, where as T2 's set up is something that they would not be, so for clarity we use T1 for displaying etc.
now my problem is, i try to transfer data from one table to another but it works sometimes and doesnt work others, i dont get any errors and i have option explicit on as well. if someone could look at this code and tell me if there are any problems that you see, the quicker the response the better.
technical specs: Access 2000 Windows 2000
Code: Private Sub insertRecMainDB(ByVal typeOfOrder As String) 'This sub will insert a new record of type O (Ordered) and put in all values from the table Order 'Also it will make a duplicate record of type R (Received) and put in same values so when later 'if there are no changes (meaning all product was received) no entry has to occur, however if you 'are missing a part then you can enter how many on the other form.
Dim yesOrNo As String Dim tempSQL As String Dim rec As New ADODB.Recordset Dim orderAmountRec As New ADODB.Recordset Dim partNameRec As New ADODB.Recordset Dim numOfRecords As Integer Dim cnt As Integer
'Checks the type of order and either checks or does not check it in the DB If typeOfOrder = "O" Then yesOrNo = "yes" Else yesOrNo = "no" End If
'Counts the number of parts we have to order, saves it in numOfRecords rec.Open ("SELECT* FROM [Order]"), conn, adOpenStatic, adLockReadOnly numOfRecords = rec.RecordCount rec.Close 'Loop to transfer data from the table Order to the table MainDB For cnt = 1 To numOfRecords
'Gets the name of each part tempSQL = "SELECT [Description] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";" partNameRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Gets the amount ordered of that part tempSQL = "SELECT [MaterialOrdered] FROM [Order] WHERE [PartNumber]=" + CStr(cnt) + ";" orderAmountRec.Open (tempSQL), conn, adOpenStatic, adLockReadOnly
'Updates that part name with the ordered amount tempSQL = "UPDATE [MainDB] SET [" + CStr(partNameRec.Fields(0)) + "]=" + CStr(orderAmountRec.Fields(0)) + " WHERE [OrderNumber]=" + CStr(txtOrderNumber.Value) + " AND [DateTime]='" + CStr(lblDateTime.Caption) + "' AND [TypeOfOrder]='" + typeOfOrder + "';" CurrentDb.Execute (tempSQL)
partNameRec.Close orderAmountRec.Close Next End Sub
Hey, I've got to make a query that displays all records whose Date/Time field appear after another tables Date/Time field. I suppose I could say this is for use in an "Item scanned into inventory, Item Scanned out of Inventory" fashion.
To purpose is to use the total items returned after the date/time to be subtracted from the total of items that were scanned in at an earlier date.
I always tend to get redundant because I'm never sure how well I explain something, it would work something like such:
So effectively the query would run, with the date/time criteria being ItemID 1's Date/Time. It would ignore the 1:00 entry as it occured before the first Item that was scanned in, and return that we have 0 ItemID 1s in inventory.
Inventory is just being implemented it was seat of your pants in the past that is why I have this dilemma we dont know how many are in stock until we check it manually but we know how much has left. This is why data before our manual check is not important to our current totals. We could have sent out 5 ItemID 1s but only have 1 in stock now, and if we use that data we would have -4 in inventory. I'm attempting to use the oldest date/time of the manual scan (table 2) as a criteria per item scanned against table 1. Hopefully, to return the present total of items on hand.
Edit* As a note, I understand how to use >=#1/1/08 1:00:00# as a criteria but for some reason I cannot swing using a field in a table as criteria. That's pretty much what I can't find on the board or in my books.
I have a table with several date fields. All the fields are formatted to medium date, but the data is being saved in to different ways. Some records are yy-mmm-dd and some are dd-mmm-yy. Each field has at least a few inconsistent dates but not always in the same record. I can't figure out how to make them consistent again. Has anyone else ever had this problem?
I am making a race results database that is supposed to sum an entrant's two times together to obtain a total combined time.
Let's say that the two times I have are '41.43' (41 seconds and 43 milliseconds, there is no colon) and '1:48.17' (1 minute and 48 seconds and 17 milliseconds), and both are currently of the short text data type in my table. How would I go about obtaining the sum of these two times? I have already tried using queries to convert the strings to seconds with no success.
I am having some kind of problem. A query in Acces that is used to find results in the DB is asking 2 time's te input.
And there is no way that the input boxes is asking to do this twice..
here is the Query,
SELECT tblHotlines.Hotlinenummer, tblHotlines.Vestigingsnummer, tblDealers.Postcode, tblHotlines.Datum_hotline, tblBestellingen.Chassisnummer, tblBestellingen.Onderdeelnummer, tblBestellingen.[Uitleverings datum], tblBestellingen.[Vervangende auto ingezet?] FROM (tblHotlines LEFT JOIN tblBestellingen ON tblHotlines.Hotlinenummer = tblBestellingen.Hotlinenummer) LEFT JOIN tblDealers ON tblHotlines.Vestigingsnummer = tblDealers.Vestigingsnummer WHERE (((tblHotlines.Vestigingsnummer) Like [Geef het vestigingsnummer op] & "*") AND ((tblDealers.Postcode) Like [geef evt een gedeelte van de postcode:] & "*") AND ((tblHotlines.Datum_hotline) Like [Geef de datum op:] & "*") AND ((tblBestellingen.Chassisnummer) Like [Geef het chassisnr op:] & "*") AND ((tblBestellingen.Onderdeelnummer) Like [geef evt het onderdeelnr op] & "*"));
Qhat could be the problem that Access is running a Query twice before getting results
I have a form with a subform in a navigation menu. Something like:
Navigation Form NavigationSubform ParentForm (header data and some unbound calculation fields) Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form PF1=10 PF2=3 Subform QTY ADJ SF1 SF2 Line 1 5 .05 10.6 112.89 Line 2 8 .14 10.38 105.15 ... Line N SF1=PF1+(PF2/QTY) SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.
While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different<< Date/Time formats in the *same* table.
Below is the date range I've entered from January 2007 to May 2007 and below has been copied and pasted directly from the datasheet. Trying to do a date sort in any fashion is out of the question until I resolve this.
I am consistent in my date input... April 6, 2007 is entered as 4/6/07, January 18, 2007 is entered as 1/18/07, February 21, 2007 is entered as 2/21/07 and so on.
However, some dates are showing as mm/dd/yyyy format while others are showing as dd/mm/yyyy format.
Note: if I choose a general date or long date format Access reads these as July, September and October dates in some cases.
Here is January (as copied and pasted from the datasheet)
01/03/2007 (these first 7 show a mm/dd/yyyy format) 01/03/2007 01/02/2007 01/10/2007 01/09/2007 01/05/2007 01/11/2007
18/01/2007 (these last 6 show a dd/mm/yyyy format) 24/01/2007 27/01/2007 27/01/2007 02/01/2007 31/01/2007
I'm getting a Run-time error 2295: Unknown Message Recepients.
I've got a DB of about 2000 clients. In testing I did a test DB with 50 random clients. Using any search criteria, it would grab those clients and open a new email with their email addresses no problem. However when I do this same thing with my complete DB of 2000 clients, I get this run time error. I'm pretty confused here, and anxious. It looks like the program is working, I just need it to run with all my clients. This is the last thing I must conquer to be finished with this project.
I have split the database, with the back-end residing on the server. Only 1 other person is working in Access right now; she's verifying the data. Today she's working directly in the back-end, could this be the problem. If it is, I'm going to have to create a front-end for her quickly.
My problem is this...I'm working on queries and forms so that the scientists who will ultimately be using this application, won't be able to go in and inadvertently change something in the tables set-up. When I'm in the back-end main data table, it says I have 2723 records.
When I create a front-end query to query all the records in that table, it says I have 2160 records.
The input form that I created with most of the same fields as the query (created BEFORE I created the query...I'll have it pull from the query now instead of directly from the table) also says we only have 2160 records.
I have a continuous subform on my main form where one text box is a hyperlink that opens a form. All is well, except....
When the mouse hovers over the text box of the top record, the cursor does not change to the hyperlink pointed finger thing (it's I-beam), even though the text in the textbox displays in hyperlink format.
If I hover over the hyperlink/text boxes of records 2 thorough x, the hand-cursor appears.
More info: 1. The hyperlink functions normally (i.e., the appropriate form opens to the appropriate record) 2. When I then return to the main form, the hand-cursor magically appears when hovering the first record. 3. I have 2 other continuous subforms on the same main form that behave the same way.
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday EXIT FUNCTION ELSE IF [PunchOut] ISNOT Between Midnight and 0559 hours EXIT FUNCTION ELSE DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
