Select statement based on Time

[Seabz420]

CR10
SQL DB

I have a main report with a subreport and a formula field called @time in each (that's how they are linked). The @time field takes the time (in a field in the DB) and removes the date and the seconds from it. Here is the code:
CTime(Mid(totext({Pages_ADV_All_Sites.Time}), 12,6) & Mid(totext({Pages_ADV_All_Sites.Time}), 21,2))
...a result of this is a time value like "1:42:00PM".

When I want to make a select statement in the subreport based on this field, I get an error that a time value is expected. The select statement I have is: {@time} = {?Pm-@time}.

Basically what all this does is round down the times and remove the dates so that I can sort the data in the subreport based on the time on the main report. Ideas?

 

[lbass]

You shouldn't have to make a select statement--the linking should automatically create this statement. If you are unable to link, it suggests that the two times are not actually of the same datatype, so you might want to doublecheck that.

-LB

 

[synapsevampire]

Try:

ctime(dateadd("s", second(ctime({Pages_ADV_All_Sites.Time}))*-1,{Pages_ADV_All_Sites.Time}))

-k

 

[Seabz420]

Ah lbass, so simple a solution yet so troubling a problem! On the Sub Report link, I hadn't selected the two different formula fields (easly missed as they were named the same). I renamed the formula fields (1 and 2), relinked the subreport properly and everything works fine now. Thanks for the help.