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

[mdommer1]

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

[mshmyob]

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

Post exactly what you wrote.

cheers,

[NeoPa]

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

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


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,

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]

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

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]

SELECT[Work Order].ID,Round(DateD iff("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

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]

SQL typed in by member

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

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.