how should I format this date?

[mirror152]

I am trying to do a query on all incidents that aren't closed. When an incident is open, it is assigned a closure date of 12-31-1840 (don't ask me why it isn't left null). I have a selection criteria that looks like this:

{incident_data_all.date_inc_closed} = Date (1840, 12, 31) and
{incident_data_addendum.Data_Entry_Date} >= {?Start Date} and
{incident_data_addendum.Data_Entry_Date} <= {?End Date}.

The problem is that its not returning any data (and I am sure that there are data there to return - when I run the same report without the "closed" criteria, I get results that would fit the original selection criteria). Any ideas as to why?

 

[AndyMc]

Do you get data if you just have:

{incident_data_all.date_inc_closed} = Date (1840, 12, 31)

by itself?

 

[mirror152]

No

 

[AndyMc]

Put {incident_data_all.date_inc_closed} field on the report without the selection and see what it prints for incidents that are still open.

Andy

 

[mirror152]

I did that and it printed all of them (open and not). What I just did was to add a formula in the supression section of the section expert for that section. Here is the formula I used:

{incident_data_all.date_inc_closed} <> Date(1840, 12, 31)

It works fine that way. I'm still curious why the slection criteria didn't work. Thanks for the help.

 

[Madawc]

Two ideas why your first formula did not work:

a) {incident_data_addendum.Data_Entry_Date} is null for the 1840 cases. This means the formula would stop and select nothing.

b) Your set-up cannot handle 1840 as a date. In SQL, some formats of date are limited to a range. See if one of the 1840 dates can be manipulated, DateAdd etc.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10