UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> My Formula Field    
 
   
jimmy0305
post Jul 31 2014, 02:29 PM
Post#1



Posts: 610
Joined: 16-March 05
From: New York, NY


Hi Everyone...
Just want to ask what am I missing here?
CODE
case
When convert(varchar, V_IMG_STUDY.BEGIN_EXAM_DTTM, 8) between '00:00:00' and '08:00:00' then 'Tour I'
When convert(varchar, V_IMG_STUDY.BEGIN_EXAM_DTTM, 8) between '08:00:01' and '16:00:00' then 'Tour II'
When convert(varchar,V_IMG_STUDY.BEGIN_EXAM_DTTM, 8) between '16:00:01' and '23:59:59' then 'Tour III'
Else ''
End as Tour_time

This is a formula field in Crystal Reports (ver. 14) when I check it, the error says "The remaining text does not appear to be part of the formula."
Can somebody please lead me to the right direction?
Basically, what I want to accomplish is to get a time range & assign it to a label.
For example: From 12 am - 8 am = Tour I
From 8 am - 4 pm = Tour II
From 4 pm - 12 am = Tour III
I am using Case statement because that's the only similar example I can get. Is there an easy way to accomplish this?
Many thanks in advance.
Jim
Go to the top of the page
 
jimmy0305
post Aug 4 2014, 11:43 AM
Post#2



Posts: 610
Joined: 16-March 05
From: New York, NY


I got it & I would like to share this info in case somebody here has a similar problem...
On MS SQL Server Management Studio, use this:
CODE
select a.BEGIN_EXAM_DTTM,case
When convert(varchar, BEGIN_EXAM_DTTM, 8) between '00:00:00' and '08:00:00' then 'Tour I'
  When convert(varchar, BEGIN_EXAM_DTTM, 8) between '08:00:01' and '16:00:00' then 'Tour II'
  When convert(varchar, BEGIN_EXAM_DTTM, 8) between '16:00:01' and '23:59:59' then 'Tour III'
Else ''
End as Tour_time --from (select BEGIN_EXAM_DTTM,convert(varchar, BEGIN_EXAM_DTTM, 8) As Tour
from V_IMG_STUDY a

In Crystal Reports XI, you need to create a Formula Field & SQL Expression Field...
In Formula Field, use this:
CODE
Select {%Tour}
case '00:00:00' to '08:00:00':"Tour I"
case '08:00:01' to '16:00:00':"Tour II"
case '16:00:01' to '23:59:59':"Tour III"

Default: "*Unspecified Tour*"

And in SQL Expression Field, create a field and name it as "Tour" and then convert the "DATE/TIME" to just "TIME" by using this:
CODE
CONVERT(varchar,V_IMG_STUDY.BEGIN_EXAM_DTTM,8 )

Hope somebody will find this useful someday...
Jim
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 01:41 AM