| Author |
Message |
RTSFO
Newbie
Joined: 10 Sep 2009
Online Status: Offline
Posts: 36
|
  
Topic: null vales in time field
Posted: 02 Oct 2009 at 2:35pm |
I have a formula:
time(0,0,0) + (Ctime({Claim.loss_time}) - (Ctime({CustomTable.time_formatted})))
But there are null values in either of the columns and while running the report, I get an error "Bad time format string"
How do I check for null values?
Thanks
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Oct 2009 at 2:40pm |
NOt exactly sure hwat you are trying to do her but you can chek for null for ISNULL({table.field})
like
time(0,0,0) + ((if isnull(field) then time(0,0,0) else field) -(if isnull(field) then time(0,0,0) else field))
|
| IP Logged |
|
RTSFO
Newbie
Joined: 10 Sep 2009
Online Status: Offline
Posts: 36
|
Posted: 02 Oct 2009 at 3:46pm |
This is what I have now, but I am still getting the error:
time(0,0,0) + ((if isnull({Claim.loss_time}) then time(0,0,0) else
ctime({Claim.loss_time})) -(if isnull({CustomTable.time_formatted}) then time(0,0,0) else ctime({CustomTable.time_formatted})))
Thanks
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Oct 2009 at 5:58pm |
|
can you explain what you want to do with this formula?
|
| IP Logged |
|
RTSFO
Newbie
Joined: 10 Sep 2009
Online Status: Offline
Posts: 36
|
Posted: 05 Oct 2009 at 10:09am |
I have two time fields one is the start of the shift and the other is the time the emnployee got injured.
I am trying to calculate the hours employee worked inthat shift before he got injured.
Claim.loss_time and CustomTable.time_formatted
So, when there are any null values in any of the fileds I am getting the error.
Thanks
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Oct 2009 at 10:29am |
Try using this...
time(dateadd('n',
if isnull({Claim.loss_time}) or isnull({Claim.loss_time}) then 0 else
datediff('n',{Claim.loss_time},{Claim.loss_time}),
DATETIME(2009,01,01,00,00,00)))
Format it as '13:23'
|
| IP Logged |
|
RTSFO
Newbie
Joined: 10 Sep 2009
Online Status: Offline
Posts: 36
|
Posted: 05 Oct 2009 at 3:05pm |
This doesn't work because my fields are not datetime fileds. I am getting an error that a datetime field is required.
Thanks
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 05 Oct 2009 at 3:46pm |
|
What is the datatype of you fields? string?
Jyothi
|
| IP Logged |
|
RTSFO
Newbie
Joined: 10 Sep 2009
Online Status: Offline
Posts: 36
|
Posted: 05 Oct 2009 at 3:48pm |
|
Yes. I am trying to attach a screenshot of my screen and I am not able to.
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 05 Oct 2009 at 3:50pm |
|
post sample data for your fields
Jyothi
|
| IP Logged |
|
|
|
