VBA to subtract form field time value from another form field time value

[chris88]

Hi all, Fairly new to Access VBA and i cant for the life of me figure this out or find anything through searches on google/this site that cover my specific problem.

I have a form that when the save button is clicked adds a new line to a table based on the fields completed within the form.

the bit im having trouble with is that when this saves i want it to poulate an additional column based on the form fields completed but have no idea how to start.

So i want form field "End_Time2 subtracted from form filed "Start_Time" to give me the new table field "Hours_Used".

both form fileds are formatted as short date and table field as date/time

Thanks

 

[NauticalGent]

Quick question: are you really attempting to break it down to the number of hours that has transpired? If so, there are a few things you will have to do...

 

[chris88]

yes. ideally i do need it as the number of hours between the 2 times

 

[NauticalGent]

Ok, quick answer based on the assumption that you are using the Now() function to populate your Start and End times:

[Hours] = ([End] - [Start])*24

 

[chris88]

thanks for the quick responses on this.

its actually a user required field on a form so is manually entered by the user.

so tried your solution and get run time error 13 type mismatch

code i used is:

rs![Hours_Used] = (Me.End_Time.Value - Me.Start_Time.Value) * 24

Table field is formatted as date/time and both fields on the form are short date so if you could help me figure out which bit needs chnaging to account for the formatt id appreciate it??

 

[NauticalGent]

Make sure all fields are formatted as general date. If so there shouldn't be any mismatches.

 

[chris88]

ok so that kind of works but it stores the Hours_Used in the table in date format so when the "end_time" is 16:00 and the "start_time" is 08:00 it updates the "hours_Used" as 7/01/1900 instead of 8 which is the actual difference.

i tried using teh format function which does format it to HH:MM but doesnt put a vlue in there and i get "00:00" instead.

 

[NauticalGent]

One thing I should have caught early. You should not be storing the hours used in a table...just the start and end time. The calculation should be done on a form or report.

Also, I do not think you have your fields formatted right. You should be seeing a date and time in datasheet view. Is this the case?

 

[missinglinq]

The DateDiff() Function is designed to do this kind of thing. Have a look at Help for the function.

Note that if you use 'hours' as the Interval Parameter, i.e. "h" you'll get just that...the difference in (whole) hours. If you want hours/parts of hours, you'll need to use 'minutes' as the Interval, "n" (note that's n...not m, which stands for 'months') and then parse it out with something like

Hours_Used = DateDiff("n", Start_Time, End_Time)/60

Linq ;0)>

 

[chris88]

that makes sense. so added the aditional field to teh form with the solution you provided as the control source of that field but the value shows as 00:00 and not the 8 hours i need?

thanks again for the quick reply on this

 

[NauticalGent]

The DateDiff() Function is designed to do this kind of thing. Have a look at Help for the function.

Which one of these icons is for the "Duh/Smack Forehead" action...jeez.

 

[chris88]

How i didnt think of DateDiff i dont know but still shows as 00:00 instead of 8

so as suggested i looked at help and basically copied your example of

=DateDiff("n",[Start_Time],[End_Time])/60

and ive tried it in both the control source and default value of the field properties and no success.

does this have to be used in VBA for it to work?? does it need a data table field value rather than the value entered in a form field to work?? or maybe its the formatting of the fields?

so i have teh format as general date for all fields which give me a result of 07/01/1900 and if i formatt them to short time i get the vlaue of 00:00

thanks for you help both

 

[chris88]

Fixed

used

rs![Hours_Used] = DateDiff("n", [Start_Time], [End_Time]) / 60

in the vba when saving teh record instead and works like a dream.

thnaks both for your help

 

[NauticalGent]

No worries, glad you got it sorted out! Make sure you click on linq's scales (not that he needs it!), he is the one who hit the money-shot.