Converting a date to a specific string format

[technonurse]

I need to suppress the printing of a medication script when the med is discontinued today. My plan is to do a conditional suppress on the sections based on {RX_stopdate} = {datadate}. My {RX_stopdate} has to remain a string field because when I convert it to a date field and try to run the report from the medical records, any date converted from a string field produces an error. So I want to say:
if Cstr (datadate MM/dd/yyyy)= {RX_stopdate) then suppress. My question is how do I get this exact format MM-dd-yyyy to show in my formula?

Technonurse

Technonurse-Spokane

 

[technonurse]

Oh, I am running Crystal 9.0. Thanks.

Technonurse-Spokane

 

[synapsevampire]

Too bad you didn't share the database type, we might have used a SQL Expression to return a valid date from the database, and use the data in it's true form.

Also when you refer to an error, state the exact error and what you tried. You were on the right track, convert it to a date first. Since you didn't state what format the date is stored in, I'll assume that you mean that it was stored in mm/dd/yyyy format.

BTW, databases that store dates as strings ALWAYS make me nervous, and the designers reasoning for doing so is generally suspect...

Rather than suppressing the date, why not eliminate those rows from the report entirely?

Use Report->Edit selection formula->Record and try something like:

not(isnull({RX_stopdate})
and
not({RX_stopdate} = "")
and
not(
cdate(val(right({RX_stopdate},4)),val(left({RX_stopdate},2)),val(mid({RX_stopdate},4,2)))
=
currentdate)

This will eliminate the rows from the database.

Creating a SQL Expression in lieu of the:

cdate(val(right({RX_stopdate},4)),val(left({RX_stopdate},2)),val(mid({RX_stopdate},4,2)))

would net the best performance, then you could reference a date in the record selection and the server would do all of the work.

-k