error with formula to zero fill a field if no data is present 2

[timISST]

IF (ISNULL({date)) = TRUE
then "00000000"
else {date};

Gives me the error of a string is expected here at the second date ( they are date fields) tried converting to str and when i did that it gives me the bolean str number date error. Any ideas?

 

[SkipVought]

hi,

Why would you want TEXT in a Date field? Dates are NUMBERS!

IF (ISNULL({date)) = TRUE
then DateSerial(1900,0,0)
else {date};

BTW, I would not use date as a field name as it is a reserve word.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and this is better

IF ISNULL(date)
then DateSerial(1900,0,0)
else date;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timISST]

this is a formatting requirement for a report that has to be done as all zeros if the field is blank if there is a date is has to be shown as 20120301 and if no data present has to be 00000000

 

[SkipVought]

Then you must convert the date value to TEXT: ToText

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timISST]

do you mean something like this?

ToText ({date});
IF IsNull ({date})= true
then '00000000'
else {date};

 

[SkipVought]

IF IsNull ({date})
then '00000000'
else ToText ({date});

BTW IsNull ({date}) is either TRUE or FALSE

When IsNull ({date}) is FALSE then ELSE is executed

When IsNull ({date}) is TRUE then the THEN is executed

If IsNull ({date}) = TRUE is redundant.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timISST]

AWESOME only thing is how would i change 6/2/2012 over to the 20120602? I cannt use the display string to do that

 

[IanWaterman]

ToText ({date}, 'yyyyMMdd');

Ian

 

[timISST]

AWESOME THANK YOU BOTH!!!

 

[timISST]

Is there a way to designate field lengths? all of this is a report that has fields that may or may not have data and filler fields that have to be there to be read by the recieveing party? The zero filled ones I have figured out the blanks I think I know what im doing but wasnt sure if there was a way to tell crystal this field is always x characters and no spaces between.

 

[timISST]

should have added that im using a lot of formulas in this in case that makes a difference

 

[IanWaterman]

Not sure what you mean can you show example of data as is and how you would like it to look.

Ian

 

[timISST]

I made formulas to lay out like filler fields and such where they may not be data present but the spacing has to be. The issue Im having is I need all the fields touching as to not create undo and unwanted spaces in the report at suggestions?

 

[IanWaterman]

If you are exporting to Excel then use vertical guides lines and make sure all fields sap to guides at both ends

Make sure all fields are same height and aligned at top and tightly clamped in section dividers and white space above or below will introduce blank rows.

Ian