Time question, 6 years still no answer....

[jaad]

There are literally thousands and thousands of entry about this subject but still after 6 years of looking and researching I haven't found a simple answer to a really simple question:

I have two fields: [time start], [time end] format as am and pm and a mask that validate the field as a short time mask.

What I need to know is how to calculate the difference in hours and in minutes?

6 years after I still can't do it! Everybody that wants to help gives me a datediff function that never works. it either gives me a bunch of number that I can't make any sense of or it only gives me hours but no minutes.

I tried formating my field so they are all alike but that doesn't work either.
I tried using "hh:nn:ss" still no go!
I tried using the datediff with a format function, nada!
I tried simply [time end]-[time start] = bunch of number and a E in there too. NO idea what that is.

In all, I have had people sending me codes, complicate formulas, and all sort of things to still having to use my desktop calculator to input the difference between the two field after 6 years.

Is there anyone, I mean anyone on Earth or beyond that can answer this simple question?????????????????????????????????????????? ????????????

[ChipR]

First, do you have a date field or a text field? I'm not sure exactly what you mean when you say "format as am and pm".

[jaad]

Forgive me I will expand:

I have a table in which I keep my time spent for jobs. It is called [task detail]

I enter my starting time for a job in the field [start time] which is on a form. the format of the time is 24 hours. when I type in 23:00 it basically returns me 11pm.

I do the same thing with my end time and it is formated the same way.

Now,,,, I can have a unbound field in form that has [end time]-[start time] and it returns me the correct time spent on a job that day.

but when I want to create a query to produce a report out of the table [task details] where my time is kept I can't get Access to calculate the difference between the two time [start time] and [end time]

i either get Error messages; a bunch of numbers that make no sense; zero, or just the hour, but if is is under one hour I get zero.

[missinglinq]

@ChipR
This question has been solved, literally hundreds of times, given the needed facts! You haven't answered Chip's questions, which are vital to us helping you! Are we talking about fields defined as Date/Time or are we talking about Text fields?

Also, are you simply entering times, or dates/times?

Lastly, it really makes little sense that you can have a control on a form display the difference in you start time/end time, but you can't have a control on your reports doing the same thing. Can you upload your database?

Linq ;0)>

[jaad]

both fields are defined as date/time...................... and formated to show time in this format (when I type in 23:00, when I change field the field shows 11pm. I said that earlier. it is time, just time. no date no text. I have an input mask that I set so there can't be any mistakes. I type in 23:00 and it turns into 11pm. I really don't know how else to explain this???????? its not a text field nor a number nor a memo nor yes or no, not an attachment nothing, just time.

I have an unbound field in my form that automatically tell me the difference between [start time] and [end time] that works perfectly, I have no problem with that. the problem is that it is showing only on form and every time I need to print a report I have to enter each day by end using my calculator.

I tried a million different things.......... I been at it for 6 years since I had Access 2003. I am using access 2007 now and I still have the same problem. I never been able to have the darn time calculated. I often thought that most people on the planet use this program to store financial numbers and the next thing after that comes to mind has to be with date and time.

Why is it so hard to get any sort of automatic function like you could have with number and text. why is date and time so backward and complicated?

Sorry for venting out,if anyone of you had had that problem for as many years as I have i think you would all feel the same way.

Final note: Why every time I ask a question about "time" people automatically assume that I am talking about "dates"? forgive my ignorance and pardon my French English is not my first language.

[missinglinq]

Because "time" does not exist as an entity unto itself! "Time" is always part of a date!

Since you've been researching this for six years without success, I suspect you need to actually post a copy of your database. There's no way that [time end]-[time start] is going to give you a correct answer.

Help us to help your.

Linq ;0)>

[jaad]

LOL well it works! I don't know how it is possible that it works if it is impossible that it could but it does. the control source of my unbound field is written as such:

=[time end]-[time start] and it gives me exactly the right amount of time I spent on a job every time. the format for this unbound field is set to "Short time." Hey! who knows? maybe I am showing a new thing nobody knew before. might have been too simple of a thing to do for anyone to think about it??? I don't know? I jsut know it works on my form.

I will leave it at that. I appreciate you guys wanting to try to help me, truly do. thanks

I unfortunately can't upload my database because the information included within is privileged unfortunately.

[neocambell]

Hi,

It is quite unfortunate that you spent such a long time to find a solution. I was using this since the day I started databases about 12 years back ;o)

But this world is sometimes like that. People keep things as secrets. However "sharing knowledge is the path to wisdom" is what I believe just as the guys at Bytes.com.

Have a look at the following link.
** Link removed as per site rules **
^ This link was removed last time.. but the solution is there ;o)

With Best Regards,

Neo

[mdommer1]

Here's a fun way to do it:

Function ShowDuration(StartTime As Date, EndTime As Date) As String   'added 1/23/08 by MJD
 
    Dim lngSeconds As Long
    Dim strDuration As String
    Dim FoundIntValue As Long
 
    strDuration = ""
    lngSeconds = CDbl(EndTime - StartTime) * 60 * 24 * 60
 
    If lngSeconds = 0 Then
        strDuration = "0 seconds"
    Else
        ' years ' 1 year = 31,556,926 seconds
        If lngSeconds >= 31556926 Then
          FoundIntValue = Int(lngSeconds / 31556926)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 year"
          Else
            strDuration = strDuration & " " & FoundIntValue & " years"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 31556926
        End If
 
        ' months ' 1 month = 2,629,744 seconds
        If lngSeconds >= 2629744 Then
          FoundIntValue = Int(lngSeconds / 2629744)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 month"
          Else
            strDuration = strDuration & " " & FoundIntValue & " months"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 2629744
        End If
 
        ' weeks ' 1 week = 604,800 seconds
        If lngSeconds >= 604800 Then
          FoundIntValue = Int(lngSeconds / 604800)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 week"
          Else
            strDuration = strDuration & " " & FoundIntValue & " weeks"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 604800
        End If
 
        ' days ' 1 day = 86,400 seconds
        If lngSeconds >= 86400 Then
          FoundIntValue = Int(lngSeconds / 86400)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 day"
          Else
            strDuration = strDuration & " " & FoundIntValue & " days"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 86400
        End If
 
        ' hours ' 1 hour = 3,600 seconds
        If lngSeconds >= 3600 Then
          FoundIntValue = Int(lngSeconds / 3600)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 hour"
          Else
            strDuration = strDuration & " " & FoundIntValue & " hours"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 3600
        End If
 
        ' minutes ' 1 minute = 60 seconds
        If lngSeconds >= 60 Then
          FoundIntValue = Int(lngSeconds / 60)
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 minute"
          Else
            strDuration = strDuration & " " & FoundIntValue & " minutes"
          End If
          lngSeconds = lngSeconds - FoundIntValue * 60
        End If
 
        ' seconds
        If lngSeconds >= 1 Then
          FoundIntValue = lngSeconds
          If FoundIntValue = 1 Then
            strDuration = strDuration & " 1 second"
          Else
            strDuration = strDuration & " " & FoundIntValue & " seconds"
          End If
        End If
    End If
 
    ShowDuration = Trim(strDuration)
 
End Function

[mdommer1]

BTW, the above post was not really intended to measure time precisely beyond the WEEK unit due to the variations in length of month and year. But it was an entertaining attempt to mimick the Time Remaining messages seen in so many places.

[mshmyob]

Not sure what the big deal is.

I just setup a sample like you claim you have.

I have a table with 2 fields (StartTime and EndTime), both set as Date/Time and formatted as Medium Time.

I enter say 23:00 as the start time and 23:24 as end time.

On my form I have 3 text boxes (no formats applied). Form is bound to the table that has the times entered. First 2 show me my times from my table and they display as 11:00 pm and 11:23 pm respectively.

Third text box control source is

=DateDiff("n",[StartTime],[EndTime])
 

Result is 24 - ie: 24 minutes time difference between the two times.

This can be applied to the report just as easily.

But since you refuse to show us your database or even the settings for your report it is impossible to help any further.

cheers,

[jaad]

how do you get it to show both "hour" and "minutes"? I can't find any documentation anywhere?

[NeoPa]

It's hard to understand why you seem to have such difficulty grasping this. This isn't a problem that's stumped the whole world for ages. It may have stumped you for a while but having seen all the suggestions I'm having a really hard time understanding why.

I ran the following code in the Immediate Pane :

StartTime=Now()
'Wait a period of time before executing the next line.
?Format(Now()-StartTime,"hh:nn:ss")

The result I got was :

00:01:13

I could have waited longer. I could have restricted the display such that it ignored the seconds. I could have done a lot of things with it, but I think it illustrates clearly how straightforward the solution is.

If there's anything about the demonstration, or the code, or anything, that you don't follow, please explain specifically what it is and I will endeavour to clarify it further.

[mshmyob]

@jaad
Simple math. Change the control source I showed you above to:

=Round(DateDiff("n",[StartTime],[EndTime])/60,0) & ":" & Round(DateDiff("n",[StartTime],[EndTime]) Mod 60,0)
 

If StartTime is 22:00 and EndTime is 23:24 then the result will be 1:24 indicating 1 hour 24 minutes. You could of course put in text to display hours and minutes instead of the ":" character.

cheers,

[jaad]

I wrote the line exactly like you wrote it and it still doesn't work. It is asking me for a "parameter" now?????

[mdommer1]

I'd suggest googling 'using functions and expressions in access' and then revisiting all of the above suggestions.

[mshmyob]

@jaad
Post exactly what you wrote.

cheers,

[NeoPa]

@mshmyob
This was not intended to be written exactly as is. The field names are different from yours. You are expected to change the field names here to match your field names :

=Round(DateDiff("n",[time start],[time end])/60,0) & ":" & Round(DateDiff("n",[time start],[time end]) Mod 60,0)

Not too complicated really.

[jaad]

thanks NeoPa. Yes I know, (I used my own field names,of course) it still asked me for parameter nevertheless, it didn't worked. Thanks for trying to help guys and gals, I guess I should resolve myself that I will have to use my hand calculator to perform this simple task but so time consuming.....

While at it I have another one that has been bugging me to no extent:
** Edit - New question split off into its own thread **

[NeoPa]

Jaad, Let me direct you a little here.

Please post exactly the code you have used (nothing sensitive - simply the attempt you have made to implement this code).

Also, please post your next question in a new thread. This subject and the handling of it is now so involved it would be seriously not clever to complicate it further by throwing another question into the mix. This is on top of it being against the site rules of course.

[NeoPa]

Alternatively, if this is still treating it as a parameter value then I can only assume that the code is running in a place where the container of the values (controls or fields) is not in scope or is not the default.

If this is the case you need to start at a simpler position and show the value directly. Just show [time start] & [time end] individually. If these aren't even available then we have problems entirely unrelated to the logic of what you're trying to do.

[NeoPa]

@jaad
You will find this at Subform.

[jaad]

I so Apologize I hadn't read the rules of the forum. i will keep it on the same question from now on. thanks for the tip.

ok long story short, the database is at the office and I am on my laptop and reason why I can show my codes. i am not a really savy computer guy as you can all tell also and wouldn't know how to do that.

The Query is as simple as it get. I have my [task details] table on top with [id] [date] [time start] and [time end] showing on the bottom part of the query. Beside those fields, I have the expression that you gave me. When I get out of Design view to see the result of the query it say that I need to type-in a parameter for the expression you gave me.

the fields are all date field set from the underlining table. the date is set to date long date and the other fields are set to be medium time field. i have a mask that I set from the table for the time fields EX: I only have to type in 2300 in order to return me 11pm. I dont need to type in the am or pm.

that's about it?

[ChipR]

You've typed something wrong in your query design. You can post the SQL from SQL view if you can't figure it out.

[NeoPa]

No worries. Try this. It will give me the info I need to try to determine what is going on here.

To extract the SQL from a QueryDef (saved Access query) it is only necessary to view the query in SQL View. This shows the underlying SQL for the QueryDef and is text (See Access QueryDefs Mis-save Subquery SQL for problems with SubQueries). When a QueryDef is open (either in Design View or Datasheet View) it is possible to switch to the SQL View simply by selecting View \ SQL View from the menu.

From here it is simple to Copy & Paste it to wherever you need it.

[jaad]

SELECT[Work Order].ID,Round(DateDiff("n",[Time Start],[Time End]/60,0)&":"&[Round]) AS Expr1,[Task Details Table].*  FROM (([Work Order] RIGHT JOIN [Work Order Details] ON [Work Order].[Work Order ID] = [Work Order Details]. [Work Order ID]) RIGHT JOIN [Task Table] ON [Work Order Details].[Work Order Detail ID] = [Task Table].[Work Order Detail ID]) RIGHT JOIN [task Details Table] ON [Task Table].[Task ID]=[task Details Table].[task ID]  Where ((([Work order].ID)=40));

as best as I can type it in this is what the query looks like

[mshmyob]

@jaad
I can see that you have missed the second calculation for the minutes. You have & ":" & [Round]). This is wrong. Copy my code and paste it into your query and change the Field Names accordingly.

You do not have a ROUND parameter.

cheers,

[jaad]

I have no choice in that matter. As soon as I write your line "exactly" like you said, the query automatically put [ ] around "Round" at the end of the line and then ask me for "Enter Parameter Value" and then I have Round below it in the dialogue with an input field.

[NeoPa]

@jaad
Typing in SQL is not really much good. As well as being so badly formatted it is very difficult to work with, it is also extremely unreliable.

It's very important when posting on a forum to Copy & Paste your code (as suggested in my post #25).

[jaad]

As I mentioned before, the database is on another computer in the office and there is no internet connection to it. I am doing the best i can with what I am given to work with and must use my laptop to talk in here to try and get help for this thing.

[NeoPa]

Ah. I hear you.

Let me see if I can do some work on it then to display in a usable form at least. At least we all know that it's not copy/pasted so will need to treat it accordingly.

I'll include the doctored SQL here when I'm done.

SELECT [Work Order].ID,
       Round(DateDiff("n",[Time Start],[Time End]/60,0) & ":" & [Round]) AS Expr1,
       [Task Details Table].*
 
FROM   (([Work Order] RIGHT JOIN
         [Work Order Details]
  ON     [Work Order].[Work Order ID]=[Work Order Details]. [Work Order ID]) RIGHT JOIN
        [Task Table]
  ON    [Work Order Details].[Work Order Detail ID]=[Task Table].[Work Order Detail ID]) RIGHT JOIN
       [task Details Table]
  ON   [Task Table].[Task ID]=[task Details Table].[task ID]
 
Where  ([Work order].ID=40)

[NeoPa]

It is now clear that some of the code suggested for line #2 has not been added (The 2nd Round is incomplete) and there's an extraneous space before [Work Order ID] on line #7.

It is important of course to get it exactly right. The SQL processor is not equipped to guess what you meant to put down. It is very exacting.

Let us know what problems you have (if any) when these have been corrected.

[jaad]

BINGO!!!!!!

you did it...... it works!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Reason why it didn't work is simple -

the line that you wrote me earlier, I didn't noticed there was a scrolling bar to it. I missed half of the expression. I retyped it like you wrote it and it worked.

Bless your sole, you have solved a 6 year old problem for me..... millions of thanks.......

[mshmyob]

@jaad
Congratulations - Good luck with the rest of your project.

cheers,

[NeoPa]

I'm very pleased for you.

Curious though, if that's what you were after, why you didn't progress with my earlier suggestion (in post #13)? Far too short & simple even to need a scroll. All part of the fun I suppose ;)

Seriously though, very glad you got it resolved in the end anyway.

[jaad]

I guess because #13 was referring to it by codes. I have no idea on how to put that in or where to put it in. I really can't make any sort of senses out of the VB code lingo. any suggestion on a good book that would take me from the start or learning to do VB? All the manuals that Ive seen so far assume that I have a background in programing and would say to DIM something when I have no idea what DIM something mean? it is really interesting work though, i would truly love to get better at this.

[NeoPa]

That's a good answer :)

In truth, I only posted it that way then because it was not yet clear what you required.

FYI: That code will also work within a SQL query. Replace the (") with (') for more correct SQL, but it will work either way.

As far as a book is concerned, I suspect that you may be better placed if you find a course. A book can teach you many things, but it can't give you the general experience you probably need. Try a book if you can't afford a course (they can be very expensive) but a course would be better for this. Sometimes you can find subsidised courses, but that depends so much on where you live. Which town or city as well as which country, so I can't make any recommendations I'm afraid. I will say you could get lucky and find something affordable. What is certain is that you won't find anything unless you look. Go on & see if you can get lucky.

[jaad]

thanks for the tip. Indeed there are no courses offered here where I live. I would need to take a ferry and go to the mainland (vancouver, canada) in order to follow the course. hahahaha isn't there a VB Code for Dummies on hte market?

[ChipR]

There is, of course, VBA For Dummies.

[NeoPa]

Good thinking Chip.

I had a quick Google and found an online tutorial that may help (VBA Tutorial (Excel)). Just as a start-point.