|
Hi,
I've got a database field with dates of birth, these are stored as numbers in the format CYYMMDD and I want to convert them into date formats for my report. Because they're stored as numbers, records without DOBs have '0's in them, and I'd like to set the formula to return nulls where the field value in the DOB is 0, is this possible? Other than that I've got the date conversion working, its just this I'm stuck on.. :)
Thanks in advance!
Jenny
|
|
Hi,
Thank you for the replies, I can't get either solution to work, though that's probably me as I'm new to the formula editor. I'd really appreciate a few pointers. This is my date convertion formula, sourced with google :) :
NumberVar input := {Command.DOBI20};
if input = 0 then 010180 else
input:=input+19000000;
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )
It works fantastically, but for null dates (stored as 0 on the iSeries server) when I export my data to Excel these have been converted to 00/01/1900. I want the cell in Excel to be empty for these values.
I've tried to add in the empty formula called {@DateTest} with
NumberVar input := {Command.DOBI20};
input:=input+19000000;
if {Command.DOBI20} = 0 then {@DateTest} else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )
But I get an error highlighting the Date(...) formula at the bottom saying 'A string is required here'.
I've tried the other solution as below, but it gives me exactly the same result as before, the 0 date values export into Excel as 00/01/1900.
NumberVar input := {Command.DOBI20};
input:=input+19000000;
if {Command.DOBI20} = 0 then Date(0,0,0) else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )
Is it a fault in the formula as I've entered it or just that Crystal doesn't like null dates?
Thanks!
Jenny
|
|
Hooray, worked it out.. :) I've added Date(..) around the reference to the empty formula, so now the formula is below and it works, after exporting to Excel the cells are empty where DOBI20 is 0 in the database. Thank you! It would be nice if there was a way to do this in a single formula just for tidiness but at least its working now!
NumberVar input := {Command.DOBI20};
input:=input+19000000;
if {Command.DOBI20} = 0 then Date({@DateTest}) else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )
|
IP Logged
