Date Field vs. Text Field

[Cooper]

When saving an excel spreadsheet to a database file I have some date fields that if they are blank, I would like it to say "Present" in the field. How would this be accomplished? Any ideas would be appreciated.

 

[kenhamady]

Are you using Crystal Reports?
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Cooper]

Yes, I am using an excel spreadsheet and converting it into a database format and then importing it into Crystal Reports. When I set up the spreadsheet I formatted some of the fields as date fields, but when the date is left blank the field was entered into the spreadsheet as Present. On the crystal report the field is blank because it is looking for a date. I tried to add code to say if null then "Present", but the field isn't actually null because the slashes are there / /. I would imagine that if I had originally set up the field as text and then had them enter the date then the present would be there, but now that it is a date field I was wondering if it was possible to handle this on the Crystal side.
Thanks,
Kathy

 

[kenhamady]

I don't think a date field can hold slashes when empty. You may not have a true date field as read by Crystal. Right click on a date value in preview and select "Browse Field Data" and see what data type comes up. This will tell us how to write the formula. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[kenhamady]

If it is a date field, and not null, you could try:

If {Date} < Date (1910,1,1)
then &quot;Present&quot;
else ToText({Date}) Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Cooper]

Ken,
When I checked on the field, it is a date field Date(2000,01,01), and it shows up as a blank on the preview in Crystal, but if I look in the database, I see the slashes.
I tried this formula field in place of the date field I had before and the date shows up if there is one, but when there isn't, it doesn't say Present, it is just blank on the preview page.
If {leadership1.3RD_END_DA} < Date (1910,1,1)
then &quot;Present&quot;
else ToText({leadership1.3RD_END_DA})
Thanks,
Kathy

 

[kenhamady]

What is the database?
When you browse your data do you see a blank in them sample list?
In your File - Report Options settings, do you have a &quot;Convert Null to Default&quot; set?
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Cooper]

The database is DBF 4 (dBaseIV)(*.dbf) A database version saved from excel. I see the dates when I browse the data field, and I don't have &quot;Convert Null to Default&quot; set? should I?

 

[kenhamady]

When you browse, does the top of the sample list include one of your empty values (blank or slashes)? Or just filled-in dates.

If you write a new formula field:

IsNull({leadership1.3RD_END_DA})

and place it on the detail band next to your date field, what does it print next to your blanke dates?

What if you write the formula:

{leadership1.3RD_END_DA} + 90

what does it print next to a blank dates?

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)