Formula always returns zero on date parameter

[tigerjade]

I've been programming for years, but just started using CR last week, so please forgive me if this turns out to be a 'duh' moment.

I have a table that holds sets of data, one per day (populated by a nightly job). The report requests a start date & an end date, and is then s'posed to pull the relevant data for that particular date from the table. The formulas pulling data for the start date are fine; however, I get all zeros for the end date's fields. Here's the formula I'm using:

if (DateDiff("d",{HistoryMonth.histDate},{?EndDate}) = 0) then {HistoryMonth.histAllMonth10} / 1000

Any idea why I'm getting zeros on this? The data does exist; I see it just fine in the system. The only difference in the start & end date formulas is the parameter name.

tia

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[Naith]

I guess what you're saying is the end date value in the report is definitely not null. So, if you strip away the other aspects of your formula, and just have

DateDiff("d",{HistoryMonth.histDate},{?EndDate})

you return a value.

Where is the else clause in your formula? I imagine that if it's rational to divide {HistoryMonth.histAllMonth10} by 1000, the zeroes are coming in on the records that don't satisfy this criteria.

Insert an else clause, and state whether you still have the problem.

Naith

 

[tigerjade]

Thanks, Naith! Nope, still get zero. The value for the particular field I edited for is 85719, so it's definitely not a zero value.

I think I found the problem. The system is pulling HistoryMonth.histDate for the StartDate parameter only, and not for the EndDate parameter as well. Can you point me to a reference where I can figure out how to get the system to pull the data for both dates, and not just the first one?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[Naith]

Can you show me your selection criteria, please?

 

[tigerjade]

You're talking about the WHERE clause from the Select Expert?

DateDiff ("d",{HistoryMonth.histDate},{?StartDate}) = 0 OR DateDiff ("d", {HistoryMonth.histDate}, {?EndDate}) = 0

Is that of any use?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[Naith]

Is this your full selection criteria? (i.e. everything in Report/Selection Formula/Record). Is it right for me to assume looking at this, that you're only interested in data where either the start date or end date is on the same day as your historical date?

If so, why have you put it like this, instead of

(Date({HistoryMonth.histDate}) = Date({?Start Date}) OR
Date({HistoryMonth.histDate}) = Date({?End Date}))

I get the impression something is missing here. If you could post your full selection criteria, if there's any more, together with an example of the data you're getting combined with the data you expect, that would be helpful.

Naith

 

[tigerjade]

That's the whole selection criteria; I just want the row corresponding to that one date. When I enter it like you have it above, it accepts it, but nothing's showing in the Show SQL Query field.

I should be getting data like this:

EndDate StartDate
Jun-05 87526 83810
Jul-05 73250 23801
...

Instead, I get this:

EndDate StartDate
Jun-05 0 83810
Jul-05 0 23801

Is there a way for me to edit the SQL Query directly? Sorry I'm so much of a pain about this.

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[Naith]

Don't worry about it, it's cool.

In your example, what is that field that's displaying the zero? I'm thinking it's the formula in your first post, not the database value.

The thing about what you expect, and what you're getting is because of what you're saying in your selection criteria. You're stating that you want records where the number of days difference between the start date and the historical date is zero OR the number of days difference between the end date and the historical date is zero... which is exactly what Crystal has given you.

The record is not going to be returned unless it satisfies one or the other of these criteria - which means either start date or end date is always going to be zero if you display both fields for each record.

Should your display formula not simply read: {HistoryMonth.histAllMonth10} / 1000?

Naith

 

[tigerjade]

You're right about the zero; it's the result of a formula. If I simply put the field there, I get the value of that field for the start date, not the end date.

I read this, took lunch, read it again & it finally made sense to my poor little head, so I tried using an 'IN' statement in the 'WHERE' clause. I still just get zeroes in the end date, and when I look at the Show SQL Query section, it doesn't show the WHERE clause at all. Is there some way I can attach that clause to the query?

tigerjade


"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[rosemaryl]

Try this:

(CDate({HistoryMonth.histDate}) = CDate({?Start Date})
OR
CDate({HistoryMonth.histDate}) =
CDate({?End Date}))

However, if what you are trying to do is pull all the data for any time on the start date or end and your historymonth.hisdate is a datetime field, do this:

Change your parameters to Date types.

Change your selection criteria to:

(
{HistoryMonth.histDate} in {?Start Date}
or
{HistoryMonth.histDate} in {?End Date}
)

Crystal will handle this just fine... and you should see it in your where clause in the Database/Show SQL Query. Here is an example of the where clause generated using a datetime field from one of my views:

WHERE
((Incident."Open Date & Time" >= {ts '2005-06-09 00:00:00.00'} AND
Incident."Open Date & Time" < {ts '2005-06-10 00:00:00.00'}) OR
(Incident."Open Date & Time" >= {ts '2005-06-24 00:00:00.00'} AND
Incident."Open Date & Time" < {ts '2005-06-25 00:00:00.00'}))

Regards,
ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[tigerjade]

Still not working. I even tried creating a new report, in case I'd screwed this one up royally. No dice. Here's what I did; maybe y'all can find where I'm missing a step:

1. Create blank report. Attach HistoryMonth table.
2. Create StartDate & EndDate parameters (Date type).
3. Use Report > SelectionExpert > Record to create this selection formula: Date({HistoryMonth.histDate}) in [{?StartDate},{?EndDate}]
4. When I choose Database > Show SQL Query, it prompts me for the two dates and then shows just "SELECT histDate FROM HistoryMonth" (I left out the punctuation and stuff for laziness's sake). No WHERE clause, even though (I think) I just defined that in step 3.

This is getting worse instead of better. Could I maybe have a bad installation of CR?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[kskid]

Change you record selection formula

 Date({HistoryMonth.histDate}) in [{?StartDate} [b]to[/b]{?EndDate}]

Save and close it
TRhen go to Database->Show SQL query and you should see the dates following the WHERE clause

-LW

 

[tigerjade]

I did, and it didn't. Please pause while I throw this computer out the window. Is there anywhere I can edit the SQL Query myself, without using a wizard or the formula editor?


thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[rosemaryl]

Dear tigerjade,


Do not modify your datetime field.

Did you copy the formula I supplied? It is not necessary to modify your field with the Date function and doing so will ENSURE it will not pass a Where clause.

Please using my exact example only correcting for field names or parameter names.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[tigerjade]

Thank you, Rosemary! I'm not blonde; I just play it on tv. The WHERE clause is now displaying nicely. I'm still getting zero on the end date formulas, though. Let me fight that a little more before I pester you guys any more...

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[rosemaryl]

Dear Tigerjade,

What do you mean you are still getting 0 for end date? Can you be more specific and post the formula that is giving you trouble.

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[tigerjade]

I think it's a layout issue. What I need is for this data to show up in this format:

end date start date
23580 128420
83150 71372

but I get the start date data in both columns of a row, and then a repetition with the end date data in both columns:

end date start date
128420 128420
71372 83150

end date start date
23580 23580
83150 83150

The formula I'm using is:

if ({HistoryMonth.histDate} = {?EndDate}) then {HistoryMonth.histAllMonth0} / 1000 else 0

I get the correct data for the start date column, but zeros for the end date column, rather than the data in the system.

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[rosemaryl]

Are you using the same formula for the Start Date column?

How have you linked these two tables?

Have you grouped by a field? If so, which field? And, are you sure that you have data for the end date?

I think more information is required...

regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[tigerjade]

It's all in the same table; there's one row per date (from a nightly job). Same formula for both columns; start date gives the right data, end date gives zero. I've tried grouping by the histDate field & not grouping; I can't see a difference in the report either way. I'm 100% positive there's data for the end date; I can see it in the table.

Does that help? Thanks for being so patient with me

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[rosemaryl]

Dear Tigerjade,

I think my brain is dull. Maybe you should explain what you are trying to accomplish. Lay it out for me. Maybe then I can help with a solution.

An example of a few rows of data would be helpful along with the goal.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762