[Solved] Report based upon query won't open

[klompje]

I created a database with two tables. One is called "TbUrenRegistratie" (Dutch for recording "working hours") and it has, among other things a date, starting time and end time.
I want to have a field that calculates the working hours, and I want to be able to have a total for each date.
I can calculate the working hours per record in a report, but don't now how to calculate all working hours for a particular date.

So I made a query:
Date
StartTime
EndTime
And I created an extra cell: ( ( ( MINUTE( "EndTime" ) ) / 60.00 ) + HOUR( "EndTime" ) ) - ( ( ( MINUTE( "StartTime" ) ) / 60.00 ) + HOUR( "StartTime" ) )
When I run the query, it does exactly what I want. So far, so good.

But then I made a simple report, based on that query.
The report won't open:
"The document "Query_Datum_Begin_Eind_totaal" could not be opened."

I then made a query that gets all the fields from the first query, made a report, and this time I'm able to open it.

But then I made another query with all the fields of both tables and two calculation fields. Another query gets all the fields, including the calculated values.
But a report based upon that second query (as well as a report based on the initial query, for that matter) shows the same error-message.

I hope this is enough information to get some help. I would really appreciate it.

Greetings,
Marco

[klompje]

The problem seemed to be that two records didn't have complete entries yet.
One missed a StartTime and one missed an EndTime.
Since the query calculates End-Start there was a problem. But I didn't notice this when running the query.
Only after I deleted some records and it worked as expected, I found this problem.

[RPG]

Hello

I think you can make more easy your calculation.

Code: Select all

SELECT 
"id",
"Naam", 
"begin",
 "eind"
,  datediff('mi' ,"begin", "eind") as "TotalMin"
,  (datediff('mi' ,"begin", "eind")/60.00) as "totaalhour",
cast (datediff('mi' ,"begin", "eind")/60.00 as decimal(5,2)) as "totaalhour2"
 FROM 
"tijdrekenen" AS "tijdrekenen"

You can find information here:
http://wiki.services.openoffice.org/wik ... _Functions
http://www.oooforum.org/forum/viewtopic.phtml?t=100641

Follow the explenation of Sliderule.

Romke

[klompje]

Hi Romke,

Thank you. I will try this.
I had been looking at DATEDIFF, but it didn't work.
And since the calculation worked, I didn't look any further.

In what way is the above solution better? Or is it just one of the many ways to get things done?

Marco

[RPG]

Hello

When your code is working then do not change it.
It was only to make clear you can do it more easy.

Romke