Selecting Time

[sjc1956]

I am doing a report where I'm listing medications that are ordered {c_medication.time_for) and the time they were charted (c_medication.entered_at). I only want to show those that were ordered at 9:00 AM {c_medication.time_for) and want all dates. The display on the report is 9/??/2007 9:00AM

{c_medication.emtek_id} = {?Pm-r_adt_core.emtek_id} and
IsNull ({c_medication.changed_date}) and
{c_medication.time_for} like ??????

Thanks

 

[synapsevampire]

Please post your version of Crystal, and the database.

Use Report->Selection Formula->Record and try:

{c_medication.emtek_id} = {?Pm-r_adt_core.emtek_id}
and
IsNull ({c_medication.changed_date})
and
hour({c_medication.time_for}) = 9
and
minute({c_medication.time_for}) = 0

Of course the 9 portion of the SQL may not pass, which is why I asked for the database type, and why you should always include that with every post.

-k

 

[lbass]

Or you could use:

{c_medication.emtek_id} = {?Pm-r_adt_core.emtek_id} and
IsNull ({c_medication.changed_date}) and
time({c_medication.time_for}) = time(9,0,0)

-LB

 

[synapsevampire]

LB's won't pass the criteria to the database either.

Create a SQL Expression which returns the time, then use that in the record selection for optimal performance.

-k

 

[sjc1956]

and
hour({c_medication.time_for}) = 9
and
minute({c_medication.time_for}) = 0

This worked like a charm. Thanks. I'll include the other info next time, thanks for reminding me.

 

[synapsevampire]

It'll work, but it isn't optimal.

You should leverage SQL Expressions to speed up the query if need be.

-k