How To Record Length Of Time With Status Value Of 3
Mar 20, 2013
I have a database which contains a field 'status'. This status takes a numerical value (1, 2, 3, etc...). Everything works great.
What I want is to monitor the length of time a field spends on particular status. Specifically, I want to record the length of time that the record spends with a status value of 3. So if the record has its status changed to 3, the system 'starts the clock' and once the status changes away from 3, the system 'stops the clock'. This must also continue if the status subsequently returns to 3 at a later date (for the same record) and cumulatively produce the total amount of time that the record spent on status 3.
Practical example
March 19th: Record is set to status 3 (for the first time)
March 21st: Record is set to status 1
March 25th: Record is set to status 3 (again)
March 27th: Record is set to status 2
In this scenario, I want visibility that the record has spent four days on status 3 altogether. I don't need exact times (nearest day will do). Ideally the system will only count working days (although this is not a must-have).
View Replies
ADVERTISEMENT
Aug 2, 2014
I have my main menu form that shows the current time and uses the OnTimerEvent for this. (Set to 1000) Issue is that when I open another form it keeps showing Calculating. It does not seem to affect performance but keeps the status bar from showing the control explanations.
View 10 Replies
View Related
Oct 22, 2007
Dear All,
I have a MAIN table which stores the most recent info of a record with
following details:
Unique_Ref_Num|Status|Dept
1 |6 | 1
and a second table called history which records changes in the main table
HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2 | 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2 | #22/10/2007 16:10#
Where Status 1 = Open, 2 = Allocated and 3 = Closed.
----------------------------------------------------------------------------------------------
I want to get the count of number of queries which are not closed
(outstanding) at any point in time.
Example: (with a time parameter)
Input | Result
22/10/2007 17:00 | 1
22/10/2007 16:05 | 0
22/10/2007 14:00 | 2
I want to achieve this with just 1 query (not by using one query within the
other) b,coz I want to further use this query from Excel VBA (write through
Excel VBA and not store the query within Access)
Any help will be greatly appreciated
--
Many Thanks
Baapi
:confused:
View 4 Replies
View Related
Dec 4, 2006
hi everyone,
just wondering if it was possible to have a box that returns the % completion of the current record depending on how much data/how many of the fields of the record have been filled out.
e.g. fields with containing data / total number of fields * 100
possible or not? if so...how is it done?
thanks, james
View 4 Replies
View Related
Dec 22, 2014
I have a query with a record id, report date and status.
How do I pull the latest record if the status is AA?
For latest record in report date I used Max in Totals. With just this max it is pulling the latest date for each set of records with the same record id.
This is a start now how do i pull the latest record that has a AA status?
View 14 Replies
View Related
Feb 12, 2007
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
View 6 Replies
View Related
Jul 20, 2006
I have a form that contains a toggle button. When a record is complete and considered closed I click the toggle button. I want to make the form background turn red if I enter into a record that has been closed. :confused:
If this is not possible, is there something similiar that would stand out:rolleyes:
Can someone tell me how to do this? Thanks!
View 8 Replies
View Related
May 4, 2005
Hi,
sorry about the unwieldy title: I appear to have reached a limit while adding fields to a fixed width text table which is imported to Access on a monthly basis using Import Conditions to define the field start points and lengths. Using the Import Conditions wizard, I cannot add any break points beyond the 495th character. Is this a known limit, or has anyone found a way around? I've tried both Access '97 and 2002. Any advice greatly appreciated.
Many thanks, Kevin
View 2 Replies
View Related
Mar 27, 2015
I have a form and I want show a message on the form when the it is locked as another user is editing the data in a particular record.
I know the record selectors show the records lock status but it a very tiny symbol which will mean nothing to the users of the database and anyway I don't want record selector bar on the form. How I would do this???
View 2 Replies
View Related
Dec 4, 2013
My form which is based on a table carrying same name doesn't append the full length of the text to one of the fields.
I don't know where the mistake is occurring or indeed I don't know if the error is in the table or in the form.
The form is showing full length of the text but when I look it in the table, it has just one or two bits of the full length.
View 4 Replies
View Related
Apr 5, 2007
hi guys,
some background on my data:
i have a table that holds Electronic Gift Card details (those plastic store gift voucher cards). it holds all details of every transaction for every card, so the card number, activations, redemptions, dates, times, transaction values etc. are all recorded in my table.
i have attached a small extraction in a spreadsheet.
a single gift card can have multiple transactions against it throughout its lifespan. there are sometimes error transactions, and i need to write a query that finds these error transactions (they are all flagged with a "1" in the [reversal flag] field, so that bit is easy) but then also then the next transaction that occurs on that card. this subsequent transaction will not be flagged with a "1", but is a reversal of the error and will have an "802" flag in the [transaction type] field.
each transaction is date stamped hh:mm:ss, but i don't know how to write a query that will
1) find gift card number that has a reversal code of "1"
2) find next transaction made on that gift card based on transaction time
3) check that subsequent transaction type is "802"
4) select both transactions
5) repeat for entire table
i can't seem to get the desired result just using a straightforward query, and i don't know much (or any!) VBA in access.
does that make any sense at all???
cheers
bazzason
View 7 Replies
View Related
Oct 16, 2006
Would anybody know how to insert a status bar on a form and control it.Need help on this.Thanks in adv.
View 1 Replies
View Related
Aug 30, 2007
I've been avoiding this project but it has come up again. I have a file given to me each month. It contains a member number and status. The status could be any of the reason codes the user comes up with. So far there are ten. I imported the file into an access table. The users need a report summarizing the status changes if any. If there are any changes; the reason code will have a X under the reason code which pertains to them. I ran the unmatched query; but that gives me if the person was added or deleted. How do I search the reason code fields to check if there have been changes. If I ask for the nulls or is not null for each field; How would I know if it was null or not null in the beginning. I need a way to check to see if for example: Smith did not have a X under reason code 1 for the month of June but does in the month of July. I hope I didn't confuse anyone. Please help
Thanks
View 4 Replies
View Related
Mar 17, 2005
Does anyone know how turn the status bar off with code. I need to turn it off when the main form opens. Then when it closes I need it to come back on...
Thanks,
Ken
View 5 Replies
View Related
Jun 24, 2005
just wanna know the steps that need to be taken to auto-insert the current date and time into separate date and time fields in a table.
thanks
View 6 Replies
View Related
Jan 18, 2006
I have done this in the past but it has been sooo long since I consistently used Access that I am in a fog:confused:
How can I create a timestamp or series thereof, so that each time data is added to a Memo field there is a date time record created? My goal is to allow people to enter notes but have the timestamp automatically either append itself to the note OR timestamp a seperate field? Open to ideas...
Thanks much!
View 9 Replies
View Related
May 3, 2006
Hi - I'm working on a query, and can't seem to get it to work correctly. I have a table which contains 2 social security number fields - one for a dependent/employee with benefits, and one for the originally insured (for example, if an employee passed away, the dependent would be on the file, and the originally insured data would be populated with the original employees data). There are also cases where the employee and dependent exist in the table at the same time. I need to check the table, record by record, to identify any cases where the two SSN's are the same in any given record. The query is currently finding an employee's SSN on their record, and also finding it in the originally insured field of the dependent's record and showing it as an error. I only need to know if they occur for the same record, not for another record in the table. (I hope this makes sense!!!)
Is there any way that I can do this??? Any help would be greatly appreciated!!!!
View 4 Replies
View Related
Nov 12, 2007
I need help to calculate time difference from the record above where ID is same. I have attached an example in excel with this request where I have if statement doing exacly what I want to do in Access. I have a table in Access that has three columns - as below
ID, Reg, TimeDiff
66646, 14/06/2007 21:33,
66646, 15/06/2007 06:03, 8:30:00
66646, 15/06/2007 12:20, 6:17:00
66646, 15/06/2007 15:08, 2:43:00
67844, 14/06/2007 04:39,
67844, 14/06/2007 09:05, 4:26:00
67844, 14/06/2007 09:08, 0:03:00
67844, 14/06/2007 16:27, 4:09:00
If ID is equal to the ID in record above then TimeDiff is equal to reg minus reg in the above recored, else blank.
Hope someone can help me with this.
Thanks, GS
View 5 Replies
View Related
Dec 15, 2005
Hi, on a database I created with probably over one hundred fields in it I have one text box called update. What I want is if ANY of the fields are altered it then updates the "update" field with date and time. At the moment I have started altering every field with creating an event proceedure in the "on change" entry which reads as below for a change to text5 box. (text141 is the update field)
Private Sub Text5_Change()
[Text141] = Now()
End Sub
Now, although this works; as you can imagine it's very time consuming going through every single field I have puting this proceedure in place. My question.....Is there a much simpler way of making this "update" field change if any part of the record gets altered ?????
Hope this isnt confusing.
I should add that each record has about 4 tabs(pages) too, so the overall record is spread over all this too. I need something that knows that anything is altered on the record and so update the "update" field
View 6 Replies
View Related
May 11, 2005
I have an orders Form with a subform for products. In the subform the user selects each product by first selecting the product category from a combo box, which filters the query for the second combo where they selects the product. Once this is done, I need the record to up date, but Access gives a run time error 3058: Index or primary cannt contain a null value.
In this case I belive the key field is the product_id form my 'order_detail_table' where this is a shared key.
I've been beating this to death (it's killing me!!) for a couple of weeks now and got nowhere. Maybe someone out there can have a look and see what my obvious mistake is ... (I've attached a cut down version of db with just tables, query and subform)
Thx.
Kev
View 2 Replies
View Related
Jan 7, 2014
I want a textbox on a form to record the time every record is printed. I used the codes below but it couldn't work. Am getting 'runtime error 2147352567 (80020009)'
Private Sub cs3_Click()
Dim mydate As Date
Me.hdates = Now()
DoCmd.Save acDefault
DoCmd.OpenReport "Copy of HH", acViewReport, , "[REF] = " & "'" & [ETGRef] & "'", acWindowNormal
DoCmd.PrintOut
End Sub
View 2 Replies
View Related
Jan 4, 2012
We have a database stored on a network drive used by a number of people across the organization over a Windows XP Network. I have an strange issue where some users get the error attached when trying to amend a record. However other users are able to amend the very same records. All the relevant user/windows permissions are identical. There is no security/permissions settings on the database for it to behave in this manner. The record seems to lock itself the moment you try to edit it.
View 2 Replies
View Related
Jan 10, 2013
I'm working on a database to create service tickets for a small IT Department. I've created a form that would be sent via email using the collect data option. The thing is that I want to send an auto-response email each time a new record is created to the person(IT user) submitting the data. What is the best approach for this?
View 1 Replies
View Related
Jul 9, 2014
I am using the Issues Template Database and would like to add a function to it.
I would like to record each time spent task/ action when working on an issue.
For example:
Tasks
- Email
- Phone Call
- Report
So, say I wanted to add one Report- time taken 30mins and record that then on an another day, Email- 10mins So record each task and time taken.
View 1 Replies
View Related
May 25, 2005
i am trying to make a cross tab query that will give me the status of the unavalble rooms in my hotel
somthing like that:
romm num today today+1 today+1 today+2 today+3 today+4 and so on...
1 X X
2 X X X
3 X X X
4 X X
5 X
thas how i will see where do i have a free rooms in a range of dates that i want to insert.
please advice,
Thanks
View 1 Replies
View Related
Apr 18, 2005
I have a database that has records at different statuses along the process, like "created", "submitted to finance", etc.
I have a form where I can choose the status from a dropdown and it will total the number of records at that status and display in an unbound field "Total".
My only problem is that when there are not any records at that status it will still display the previous statuses total. Am I missing something in my code?
Private Sub status_AfterUpdate()
If Not IsNull(Me![status]) Then
Call CalcTotal
Else
Me![Total] = 0
End If
End Sub
Private Sub CalcTotal()
On Error GoTo Err_CalcTotal
Dim db As Database
Dim rs As Recordset
Dim Q As QueryDef
DoCmd.Hourglass True
Set db = CurrentDb()
Set Q = db.QueryDefs("qry_total")
Q.Parameters("[status]") = Me![status]
Set rs = Q.OpenRecordset()
rs.MoveFirst
Me![Total] = rs![Status Total]
Exit_CalcTotal:
DoCmd.Hourglass False
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_CalcTotal:
Resume Exit_CalcTotal
End Sub
Thank you ahead of time for your help!
Adam
View 8 Replies
View Related
