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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.
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.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
if {UPR00100.DEMPINAC} = #1/1/1900# then "~" ELSE ToText({UPR00100.DEMPINAC})

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
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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.

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?
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
if {UPR00100.DEMPINAC} = #1/1/1900# then "~" ELSE ToText({UPR00100.DEMPINAC}, 'yyyyMMdd')
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.

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.