Anybody??? I'm desparate and not even IBI can figure this out yet. Good challenge for you gurus.

Thanks.

Jimmy
==================

HI everyone,

I'm new to your group and I'd appreciate any help with the following problem. I have IBI working on this too, but I figured I'd try the other experts too.

In my database (4D), a date of 0000/00/00 is a perfectly acceptable value, however WebFocus wants to treat these as MISSING and displays my NODATA value or blank. I've used the appropriate SET commands with no help. The off-the-shelf application we use will initially store that value instead of a null value and it remains that way until a user changes it to a valid date (like 2004/02/12) or even back to 0000/00/00. I need to be able to query for the value of 0000/00/00 in my reports (checking to see if it's either their or in some case, not their for a particular field in a record) and cannot seem to figure out how to set the report up to accept and be able to include or exclude and display records with this value in my queries and reports. I suspect a DEFINE is needed, but so far neither IBI Tech Support or myself can figure out how to set it up. Thanks in advance.

Regards,
Jimmy

Anybody??? I'm desparate.

Thanq

I have some dates in my system also that are 00/00/0000 (just different format). So I was trying to figure out something that may help. I have a couple questions....

I'm assuming you have some dates that are actually null and you need to keep them that way, correct?

Is there another field that you could concat to the date make it unique and then query on that? This could work I think. For instance in my database I could concat the '0000/00/00' date to aonther field that wouldn't be there for a date that was actually null. Hope this makes sense. I'll keep trying...

See if i understand you correctly. Focus uses SMART dates, they are serial dates, just like in Excel, the number of days elapsed since 1/1/1900 or 12/31/1899 until the specified date.
If your specified date comes in as 00 00 0000, then focus tries to make a serial date out of it and cant. neither can excel, i don't believe.
so you can make a second date in your database BEFORE you translate your legacy date into a SMART date, and make that date the Alpha equivalent(A8)... or even A10 00/00/0000 if you really want to keep the / marks.
then do your testing against this new variable, while keeping the SMART date for date calculations and other cool date stuff. I do it this way for data from a legacy system which allows Feb 31st to be entered as valid! [yikes! No validation, so i have to check the actual alpha entry.] Works for me, hope it helps.

Thanks Victoria and Susannah. I appreciate your efforts. Ufortunately, my date values of 0000/00/00 are absolutely valid and not null or blank or missing, just that value. So when I add my WHERE clause, I might want to restrict the records returned by entering 'WHERE xdate EQ 0000/00/00' and I want back all records where that condition exists. You're right about the base date and WF wants to convert my 0 dates to 1900/12/31, which is the base date for WF (or so I'm told by NY). If I include a date field in my report and it comes across a record with a date of 0000/00/00, the value is assumed to be MISSING and my NODATA is printed. Even NY is having fits with this one. Unfortunately, I cannot add fields to the table either because it's an off-the-shelf application and database and I don't have access to the structure.

If anyone has any ideas, I'd truly appreciate it.

Jimmy

Jimmy, you need to edit the application to define a virtual variable, this is the stuff focus is the world's best at fixing. But unless you can 'access the app', there's nothing you can do.

I understand that 0000/00/00 is perfectly valid, however it appears that WebFOCUS thinks the value is null.

Consequently, one of these should work for your WHERE:
WHERE xdate IS MISSING;
WHERE xdate EQ MISSING;

If that works, then this should work when you display the date field in a report:
COMPUTE NEWDATE/A10 = IF xdate EQ MISSING THEN '0000/00/00' ELSE EDIT(DATECVT(xdate, 'YYMD', 'A8YYMD'), '9999/99/99');

If the WHERE works, but not the COMPUTE, we might need to tweak things a bit. If the where doesn't even work... well, let's not go there yet.

Let me know how things work out.
Regards,
Michael