Date field coming down from the data

[QueSpr04]

CR10, ODBC[RDO] connection, Informix database

Can anybody tell me how to report a date from the database that has no data? Meaning, the office is only open from Monday thru Friday so only data is collected on these days. I am trying to display on the report "Report for: August 1, 2004 To August 31, 2004", but of course August 1st is on a Sunday. I have been using the formula dateserial(year({cr_del_rpt.e_date}),month({cr_del_rpt.e_date}),1) for the first day of the month which starts on a Sunday, and dateserial(year({cr_del_rpt.e_date}),month({cr_del_rpt.e_date})+1,1-1) for the last day of the month. But when I select the program to run for only one day,(i.e. only for the day of August 10th) it will not show that day only. The key is that I cannot use a parameter. The date has to come down from the data because the users select the dates for the program via .net. This is an EMERGENCY! Please can anybody help?

 

[synapsevampire]

The obvious approach which you've no doubt considered is to create a dummy row in the table for those days.

Other approaches include the long term approach as described in my FAQ:

faq767-4532

You state that you cannot usea parameter, yet somehow you're selecting for only one day, perhaps you should elaborate on how this is done? Are you hardcoding it?

A little more background information would help to advise you further.

-k

 

[QueSpr04]

Yes, I am hardcoding it. The first formula that I used was
dateserial(year({cr_mailfee.e_date}),month({cr_mailfee.e_date}),1) for the first day of the month and dateserial(year({cr_mailfee.e_date}),month({cr_mailfee.e_date})+1,1-1) for the last date but that formula would not work for selecting one date. So I had to revert to using minimum ({tablename.e_date}) and maximum ({tablename.e_date})this formula will allow the selection of one day, but it will not grab the first or last date of the month if it is on the weekend.

 

[synapsevampire]

Neither formula works for selecting only one date.

If there are 100 different e_dates, then you won't get just one date.

I don't understand the intent, nor why a parameter is undesirable.

I think that you should consider a different approach to architecture, which requires that you share meaningful technical information:

Crystal version/edition
Database/connectivity used
Example data
Expected output

BTW, this formula doesn't work anyway:

dateserial(year({cr_mailfee.e_date}),month({cr_mailfee.e_date}),1) for the first day of the month and dateserial(year({cr_mailfee.e_date}),month({cr_mailfee.e_date})+1,1-1)

When December hits, the month will become 13 and error because you add +1.

As for the basic question "Can anybody tell me how to report a date from the database that has no data?", that's simple, there's nothing to report

Rather than using text descriptions for a specification, post examples.

-k