CRYSTAL REPORTS IF STATEMENT WITH DATE AND TEXT

I need a formula that will return a null date 1/1/1900 with the character "~".   This is what I have so far, but it comes back with an error says that a Date and Time are Required Here.

if {UPR00100.DEMPINAC} <> #1/1/1900# then {UPR00100.DEMPINAC} else "~"

I need the result to be the date or "~"
Paula WardControllerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
An IS statement MUST return the same data type on both branches.

If one of the branches requires a text ("~") you should convert the date in the other branch to text as well.  Use the ToText() function for that.
0
Paula WardControllerAuthor Commented:
I have tried several combinations of the ToText function, but I am doing something wrong.  I don't usually have to do this sort of thing.  Any chance you can give me that formula using my formula above.
Thanks in advance.
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
if {UPR00100.DEMPINAC} = #1/1/1900# then "~" ELSE ToText({UPR00100.DEMPINAC})
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

James0628Commented:
Ido is correct (naturally :-).  I'll just add that you can add an argument to the ToText function to control the format of the date (and time, if it's a datetime field) in the string.  If the default format isn't what you want, look for ToText in the CR Help.

 James
0
Paula WardControllerAuthor Commented:
Just realized what James comment meant.  I need to add the argument to change the date format to YYYYMMDD before it goes ToText.  Can you tell me how to do that?
thanks
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
if {UPR00100.DEMPINAC} = #1/1/1900# then "~" ELSE ToText({UPR00100.DEMPINAC}, 'yyyyMMdd')
1
James0628Commented:
I don't have CR installed on this machine, but I think Ido's got it.  And, like I said, you should be able to look up ToText in the CR Help to see a complete list of the formatting options.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.