ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> How to find date 00/00/00
http://www.istechforum.com/YaBB.pl?num=1111011266

Message started by David Waldmann on 03/16/05 at 14:14:26

Title: How to find date 00/00/00
Post by David Waldmann on 03/16/05 at 14:14:26

I am trying to get a report that shows CMACCT records where there is no date entry. It shows in CM-A as 00/00/00, but when I select Date (0,0,0) I don't find any records at all. I tried using > Date (1980,01,01) and that didn't pull any up either. Any suggestions?

Title: Re: How to find date 00/00/00
Post by Kelloggs on 03/16/05 at 15:24:33

There is not such a thing as '00/00/00'
Also, Date fields on Pervasive are not Null/Empty
To be honest no idea what they are

I suggest you to use something like this to query Pervasive and download it to either MS Access or MS Excel

SELECT
MTWO_WIP_WOPRE AS WOPRE,
MTWO_WIP_AFIN AS AFIN,
SIGN (WEEK(MTWO_WIP_AFIN)) as MyNull
FROM "WORKORD"

There the column MyNull becomes a null value aka '00/00/00'

ciao,

Kelloggs

Title: Re: How to find date 00/00/00
Post by aricon on 03/16/05 at 15:24:47

I would suggest using Access and an expression in the query to convert the values to a string value. That should be able to be reported on.

Title: Re: How to find date 00/00/00
Post by kevind on 03/16/05 at 17:01:35

This SQL will return all rows that do not have a valid date:

SELECT "BKCMACCT"."BKCM_ACCT_START", "BKCMACCT"."BKCM_ACCT_CODE"
FROM "BKCMACCT"
WHERE "BKCMACCT"."BKCM_ACCT_START" not in
(SELECT "BKCMACCT"."BKCM_ACCT_START"
FROM "BKCMACCT"
WHERE ("BKCMACCT"."BKCM_ACCT_START" > '1980-01-01'))

This uses a sub-query to create a list of rows that have a date. by selecting all rows that are NOT IN this sub-query,
only the rows that have no date are returned.

This SQL could be used with Access or Crystal.

Title: Re: How to find date 00/00/00
Post by aricon on 03/16/05 at 17:04:58

Kevin,

I like it! Reverse selection....good idea!

Title: Re: How to find date 00/00/00
Post by David Waldmann on 03/17/05 at 09:33:41

Well, thanks for what I guess is a great idea, but I have to reveal my ignorance of SQL. I tried fooling around with it for a while and couldn't get anywhere, except to prove that a little knowledge can be a dangerous thing. I ended up just sorting the records by start date, and all the ones that were blank showed up at the beginning of the report. It gave me what I needed even if it is crude.

Title: Re: How to find date 00/00/00
Post by kevind on 03/17/05 at 16:24:46

That is another perfectly acceptable way!! :D