Hi,
I'm attaching a stripped down version of a database that contains a report that needs to add the time for different activities. I'm not sure what I'm doing wrong to get the error messages.
Thanks,
Leesha
Report not adding time
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Report not adding time
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Report not adding time
I have been mucking around and found that the addition of all times using NZ screws it up.
I think it's because you are adding times up, and i thgink you should convert time to minutes, add that, then convert back to hours and minutes.
That doesn't explain the few that are ok though.
I think it's because you are adding times up, and i thgink you should convert time to minutes, add that, then convert back to hours and minutes.
That doesn't explain the few that are ok though.
-
- Administrator
- Posts: 78533
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report not adding time
In the query qryTimeSheetInitialQuery, the Totaltime field is not a time, but a text string because you use the Format function. You should not use the Format function there, but set the Format property of the column to a time format.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Report not adding time
Thanks! That did it!!
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Report not adding time
I don't know if i should start a new thread but it relates to Leeshas database i donloaded.HansV wrote:In the query qryTimeSheetInitialQuery, the Totaltime field is not a time, but a text string because you use the Format function. You should not use the Format function there, but set the Format property of the column to a time format.
Thanks for correction above, I was on the wrong track there.
I have a problem, check out the jpg, it shows the word "Test" in TimeSheetComments in query qrytimeSheetInitialQuery but in query qryTimeSheetCrosstab is shows chinese characters.
This is a memo field.
I first noticed this problem when i inherited a database developed by a chinese guy and it showed similar problems.
Is this some kind of parameter held in rhe Registry or something or am i just going looney?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78533
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report not adding time
The TimeSheetComments column displays Japanese (not Chinese) characters as soon as you group by this field. This is a known problem - see Contents of the Memo field may appear as control characters when you use the GROUP BY clause on the Memo field in a query in Microsoft Access. It has apparently existed since Access 2000
As a workaround, change Group By to First in the Totals row.
As a workaround, change Group By to First in the Totals row.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Report not adding time
Thank you Hans. And thanks for the invite to this forum.
-
- Administrator
- Posts: 78533
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Report not adding time
You're welcome, Pat. It's good to have you here.Pat wrote:And thanks for the invite to this forum.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Report not adding time
Hi Pat,
The tables in this database are sql 2005 tables. I imported them to upload them for help. The comment section is fine in the original database. I figured it had something to do with the nvarchar(500) format in sql being converted to a memo field in Access.
Leesha
The tables in this database are sql 2005 tables. I imported them to upload them for help. The comment section is fine in the original database. I figured it had something to do with the nvarchar(500) format in sql being converted to a memo field in Access.
Leesha