Date update fuction

[bibbob66]

I have a form that I would like to calculate the time taken since the last input was made. We have a vessel that fills between either two days or sometimes a week. I have a field to input the time the vessel was full and would like to have the "time taken to fill" field to update. I have very limited experience with access.
Please let me know if you require more info

Regards

Bob

[missinglinq]

I assume you have a start time/date or at least a start date field. This will give you the fill time in hours, assuming you include time in your start and stop fields.

Private Sub EndDate_AfterUpdate()
 Me.FillTime = DateDiff("h", Me.StartDate, Me.EndDate)
End Sub
 

To get it in minutes you would substitute the n for the h.

To get it in days you would substitute the d for the h.

Note that using the d for day will count any part of a day, even if the fill ended at one minute past midnight on the last day.

To get days and part of days, you could use

Private Sub EndDate_AfterUpdate()
 Me.FillTime = DateDiff("n", Me.StartDate, Me.EndDate) / 1440
End Sub
 

which calculates the number of minutes and then divides by 1440, the number of minutes in a day.

*********************************

Also note, please. Your duplicate posting of this question/problem has been deleted! Please refrain from this prohibited behavior in the future.

From FAQs

Do Not Double Post Your Questions

Double posting is where you start a thread on a topic and then for some reason start another thread on exactly the same topic in the same forum. Please do not do this because

• It makes it very hard for people to answer you especially if there are answers happening in all the threads you have started because they have to read 2 or more threads in order to see what has already been said.
• It swamps the forum with your problem resulting in less attention for the other threads.

If you feel for some reason that you post has been overlooked (for instance it hasn't had any replies) please do not repost the question. Post a message to the thread you started, simply saying "bump" and this will move it back to the top of the thread list for the forum.

Thank you for your attention in this matter

Welcome to Bytes!

Linq ;0)>

[bibbob66]

I have a field for "time tank full"
This would be the same time as the next new tank started to fill.
A new record is created every day if it is used or not.
I would like the "time taken to fill" feild to update when the tank full time is input if thats possible without having a time new tank started to fill feild?
Hope thats clear to understand?

Thanks for making time to reply

regards
bob

[maxamis4]

I assume based on your answer that you are not to familiar with access VBA code?

missinglinq has given you the answer to do this calculation. If you do not understand the VBA please let us know so that we can give you better guidance.

[topher23]

max, the question bob is posting is a bit more complex than that.

bob, let me run this by you to see if I understand it.

You create a record when the tank is full to state that it is full and when. Then, the next time the tank is full, you create a new record stating that it is full, and when. What you are looking to do is get the difference in values between the times on the separate records and display that as a value on the form. Now for the big question - do you plan to store the elapsed time in the database itself or calculate it on the form each time it pulls up?

If you plan to save it, the code is fairly easy.

Private Sub Time_Tank_Full_AfterUpdate ()
Dim dtLastTime As Date
    dtLastTime = DLast("[Time Tank Full]", "[tblWhatever]", "Not IsNull([Time Tank Full])") 'replace with your actual field and table names
    If Nz(Me.strElapsedTime)="" then me.strElapsedTime = DateDiff("h", Me.[Time Tank Full], dtLastTime) 'again, replace with your actual field names
End Sub
 

Run this code as part of your "Time Tank Full" AfterUpdate event. It's not perfect, but it should give you a good start.

This code will only work properly if you save "Time Tank Full" as a complete Date/Time and not just as a time. Also, you would have to save it as a field, otherwise it would pull up the wrong "Time Tank Full" every time you looked at old data.

[topher23]

After some more thought, there is a way you could do this using recordsets that might work a bit better.

Private Time_Tank_Full_AfterUpdate()
Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT [Time Tank Full] FROM tblWhatever WHERE RecordID < " & Me.RecordID & " AND [Time Tank Full] Is Not Null ORDER BY RecordID DESC;", dbOpenSnapshot)
    Me.strElapsedTime = DateDiff("h", Me![Time Tank Full], rs![Time Tank Full])
    rs.Close
    Set rs = Nothing
End Sub

This assumes that you are using an autonumber field called RecordID to identify your records and that the table is sorted by RecordID, along with the other assumptions in the last post, but it shouldn't be as easy to break as the other code.

[bibbob66]

Topher23
Thanks for your time looking at this.
My skills on this are very limited.
A new form is saved every day.
I would like to save the time tanken to fill field so it can be viewed when required via a database view function.
Hope thats clear enough to understand?

Thanks again

Bob

[topher23]

Bob, did you make this application or was it someone else? Because it sounds like you are completely lost now.

[bibbob66]

All my own efforts im sorry to say.
A new form is created each day <=Now() And >#25/09/2009# in my query.
I have a field for "time tank filled" on the form and would like it to update a "time taken to fill" field on the form.
Im not lost yet but its getting dark

bob

[topher23]

Okay, your terminology is a bit off, so I'll try to go slow with this.

A form is something that is used to view, change, add and delete records. A record is a row in a table in the database. Records from one or more tables can be put together into a query that holds the data "behind" the form, or a form can just reference a table to get its data. This is called the form's RecordSource, found in the Properties of the form in Design view.

If you're with me so far, we're doing good. Now, when you a say a new form is created every day, I'm going to assume that means a new record in a table or query that acts as a recordsource for the form. If that's not what you mean, let me know immediately - that would mean really big problems.

So, here's what you need to. Open, in design view, the table that holds all of the tank filling information. Add a new field called lngTimeTakenToFill to the table. Make sure this new field is a Long Integer data type, and save it. Making the name start with "lng" is a naming convention that says the field contains a "Long Integer" variable type, which is a Number variable type that allows a very large whole number. Not putting spaces in your field names is a point of protocol that makes programming much easier.

Now, make sure you have a "Primary Key" for your table. This should be an Autonumber field that is the first field in your table. If you don't have one, create one called RecordID and set it to be the Primary Key for the table. If you already have an Autonumbered Primary Key, use the name of that key instead of "RecordID" when you put in the new code (below).

Save and close your table.

Now, open your form in design view. Add the new new lngTimeTakenToFill field to your form. In the new field's properties, go to the data tab and change Locked to Yes and Enabled to No. Now click on your "time tank filled" field on the form. In the Events tab of the Properties dialog, click on "After Update." There should be an ellipsis (...) button at the end of the field. Click that and select "Code Builder."

Now, do you have separate fields for the date and the time? If so, paste this code into your new, empty VBA subroutine.

Dim rs As Recordset 
    Set rs = CurrentDb.OpenRecordset("SELECT [Date Field], [Time Tank Filled] FROM tblWhatever WHERE RecordID < " & Me.RecordID & " AND [Time Tank Filled] Is Not Null ORDER BY RecordID DESC;", dbOpenSnapshot) 
    Me.lngTimeTakenToFill =  DateDiff("h", rs![Date Field], Me![Date Field]) + DateDiff("h", rs![Time Tank Filled], Me![Time Tank Filled]) 
    rs.Close 
    Set rs = Nothing 
 

If your "time tank filled" field holds both the date and the time together (as in the Now function), use this code:

Dim rs As Recordset 
    Set rs = CurrentDb.OpenRecordset("SELECT [Time Tank Filled] FROM tblWhatever WHERE RecordID < " & Me.RecordID & " AND [Time Tank Filled] Is Not Null ORDER BY RecordID DESC;", dbOpenSnapshot) 
    Me.lngTimeTakenToFill =  DateDiff("h", rs![Time Tank Filled], Me![Time Tank Filled]) 
    rs.Close 
    Set rs = Nothing 
 

Remember to rename anything in the code that doesn't match your database, like the table name from tblWhatever to the actual table name and [Time Tank Filled] to the actual field name. Note that, if your field has spaces in it, you have to use the brackets [] when you reference it or your code won't work.

Now, save your form and give it a try. what should happen is, when you make a change to the "time tank filled" field, the "time to fill tank" field will populate itself with the number of hours it took to fill it. If you want minutes, change the DateDiff function in the code from "h" to "n" ("m" is reserved for "months") and try it again.

Now, to populate your old data, you can add this code to the form's "On Current" event using the same "Code Builder" process:

If IsNull(Me!lngTimeTakenToFill) And Not IsNull(Me![Time Tank Filled] Then Time_Tank_Filled_AfterUpdate()
 

As before, if Time_Tank_Filled_AfterUpdate() isn't the name of the subroutine that you just built earlier, change it to the right name. Now, if you pull up an older Tank-filling record, it will see that the lngTimeTakenToFill field is empty and run the code to fill it. Pretty spiffy, huh?

Well, that's probably more guidance than I should've given, but I just couldn't help myself. You definitely need to spend more time with tutorials and the Access help file. Check out Microsoft's website or functionx.com, they are great resources for learning to use Access.

[bibbob66]

WOW
Thanks for your help I will follow this tomorrow.
I am already aware of the basics but thanks for so much detail.
I will let you know how I get on

Regards

bob

[bibbob66]

I get a compile error method or data member not found showing the & Me.RecordID item as the fault?

Private Sub Time_Trade_tank_full_AfterUpdate()
Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT [Date], [Time Trade tank full] FROM tankfills WHERE RecordID < " & Me.RecordID & " AND [Time Trade tank full] Is Not Null ORDER BY RecordID DESC;", dbOpenSnapshot)
    Me.lngTimeTakenToFill = DateDiff("h", rs![date], Me![date]) + DateDiff("h", rs![Time Trade tank full], Me![Time Trade tank full])
    rs.Close
    Set rs = Nothing

[topher23]

Ooops! Sorry. I forget to mention that.

You will have to put the RecordID field on your form and set its Visible property to False (unless you want it visible, that is).

[bibbob66]

This is now the new error?
Sorry to keep asking for help if its taking to much of your time I fully understand.

Compile error method or data member not found?

Private Sub Time_Trade_tank_full_AfterUpdate()
Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT otTankStatus.Date, [Time Trade tank full] FROM tankfills WHERE RecordID < " & Me.RecordID & " AND [Time Trade tank full] Is Not Null ORDER BY RecordID DESC;", dbOpenSnapshot)
    Me.lngTimeTakenToFill = DateDiff("h", rs!otTankStatus.date, Me!otTankStatus.date) + DateDiff("h", rs![Time Trade tank full], Me![Time Trade tank full])
    rs.Close
    Set rs = Nothing

[topher23]

No worries, it's worth it for the help I've gotten from this forum (invaluable).

First, find out exactly which line the error is occurring in (it should be highlighted when you hit Debug after the error occurs).

If the error is occurring in the line that creates the recordset, make sure all of your fields are correct. It looks like you have the Date referenced from the table otTankStatus, but the FROM statement references tankfills. Is tankfills a query? If it's a table, you need a JOIN between the two tables in the recordset or it won't work. If it's a query, make sure all of the referenced fields exist in the query.

You could try re-creating the recordset in the database as a new query in Query Designer. If it works as a query, save the query and change the recordset to the name of the query. To reference the RecordID on the form in Query Designer (Me.RecordID in the recordset) you'd set the Criteria field to > Forms!(Form Name)!RecordID

[NeoPa]

@BibBob:
Please remember to use the CODE tags whenever you post any code. It's much easier to follow that way.

Also, consider giving the line number an error occurred on when posting that information. Also much easier to deal with.

Lastly, you may find it helpful (it will help others to help you - you therefore get better help more quickly) to post your meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

@Topher:
Nice going here again. We'll have you as an expert sooner rather than later if I'm any judge :)

[topher23]

@bibbob66
Don't stress about NeoPa, bob, he's just doing his job as the forum cop. It's best to keep a thread going no matter how confused things seem to be getting.

Date and Time are Access reserved words, meaning that they should never be used as field names. This is where Leszynski-style naming conventions come in handy http://en.wikipedia.org/wiki/Leszyns...ing_convention. I use my own variant of strict Leszynski, with "dt" as my prefix for date/time values, so my tables tend toward field names like dtDate or dtTime at the very least, but usually with more detail, like dtDateEntered or dtTimeCompleted.

Again, spaces in field names are bad, because they have to be encapsulated (wrapped in brackets) in order for Access to be able to evaluate them. If your database isn't too big already, now would be a good time to fix things and start practicing good design skills.

Keep at it. I can smell smoke, but I hope it means something good's cooking and not that your brain is getting totally fried.

Please let us know what errors come up when you get the design worked out.

[bibbob66]

Not sure how I ve done it but it works great!!
Thanks for your help and seeing it through to the end.
Should help in my performance review

[topher23]

Glad to hear it works! If you want to get a better understanding, use the help file to study the different functions. One day, grasshopper, you too may be master. :-)

[NeoPa]

@topher23
That dates you Topher ;)

Signed -Another fan.

[maxamis4]

bibbob66,

I am glad you found the forum helpful. Sorry I couldn't provide more help. What I will offer now is a link to a great training site for MS Access. I hope that you take the opportunity to review it and learn a little more about access and its power. Good look to you

http://cisnet.baruch.cuny.edu/holowc...accessall.html

[NeoPa]

That looks like a very useful link Max.

We do quite often get posters who are keen to go off and learn up before asking too many questions. I've made a note of the link ;)