Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys,
I'm trying to improvise on a report but due to the lack of knowledge I am unable to do so, this is my code:
VehiTable:
Load "Maint_ Item Description",
"No_" as No,
date("Starting Date") as "StartDate",
date("Ending Date") as "CompleteDate",
Month("Starting Date") as "Month",
Year("Starting Date") as "Year",
Description,
"Maint_ Item No_" as "Vehicle No",
Remark
where Year("Starting Date") >='2004';
SQL SELECT "Maint_ Item Description",
"No_",
"Starting Date",
"Ending Date",
"Maint_ Item No_",
FROM SQL-database"
where "Maint_ Item No_" LIKE 'VH%';
The output shows "1-1-1753" as the "Ending Date" if no value is recorded.
My point is to change it to showing "Null" instead of the "1-1-1753".
Anyway to correct this?
hi,
try this code
if(len(trim(date("Ending Date") ) =0, nul(),date("Ending Date")) as "CompleteDate"
Deepak
A small correction. It should be NULL().
Hey Deepak,
Not sure if I am doing it correctly:
if(len(trim(date("Ending Date") )=0, NULL(),date("Ending Date")) as "CompleteDate",
it returns "Error in expression: Len takes 1 perimeter"
HI,
You need to add one more bracket. ")"
if(len(trim(date("Ending Date") ))=0, NULL(),date("Ending Date")) as "CompleteDate",
Hmm, now it says "Field names must be unique within table"
comment the above field
date("Ending Date") as "CompleteDate" or give a different name to it.
Hi, the code went through, but the date still shows as 1/1/1753
Do you mind explaining the code "if(len(trim(date("Ending Date") ))=0, NULL()," to me?
remove the date function
if(len(trim(("Ending Date") ))=0, NULL(),date("Ending Date")) as "CompleteDate",
len is used to check the length of a field . trim is ued for removeing spaces(if any).
We arechecking if The length of field is equal to zero then put null values else use the field.
We need to remove the date function.
I see, perhaps the database has assigned null date as "1-1-1753" thus the code didn't work.
Thank you so much for your help.
