Between Dates Problem

[BeckyLee]

Here is my problem I have data that needs to be shown in a report based on the effective start and end dates. I know that I will have difficulty explaining this so here is an example:
{Start} - Start of the period
{End} - End of the period
{Date}- Date of the data being shown in the report
All three of the fields are of Date/Time but there are no time values so crystal defaults with 12:00:00 AM.
Examples:
A) {Start} = 1/1/02 {End} = 12/31/03
B) {Start] = 7/31/02 {End} = 12/31/02

1.) {Date} = 3/31/02 fits into A
2.) {Date} = 8/31/02 fits into B
3.) {Date} = 8/31/03 fits into A

I know that I can't use a simple If-Then-Else statement and I am not 100% familiar with crystal's syntex. I am using CR 8.5

Thank you,
Becky

 

[BlurredVision]

Is this what your looking for Becky?

The below can be placed in your details section, then you can group on that....

IF {Start} > CDateTime (2002, 01, 01,00,00,00) AND
{End} < CDateTime(2002,01,31,00,00,00) THEN
'A' else
if {Start} > CDateTime (2002, 02, 01,00,00,00) AND
{End} < CDateTime(2003,01,31,00,00,00) THEN
'B' Brian

http://www.crystalinfo.net

 

[Naith]

Your A Start and your B Start has a 5 month intersection where A and B are covering the same period. (July 31st 2002 - Dec 31st 2002) unless you have a typo in your post.

Just a quick note that when you're including times in your date ranges, your end period time should be 11:59:59.

Naith

 

[BeckyLee]

Sorry Brian,
for not being clear but the start and end dates change according to the record and date field is actaully a parameter in the report. So, based on the date field I want to return only the reord that the date appropriately fits in between the the start and end dates. The A & B were exaples of the possibly records while the 1, 2 & 3 are possible dates. I have the date in two formats one is just the number of the month and the other is in date/time format.

 

[BeckyLee]

Naith,
I realize that period A and B are covering the same time period. That is one of my problems in trying create a formula. If they weren't then I could do a simple if-then-else statement but I am struggling with trying to fit a date into the appropiate period.

Becky

 

[lyanch]

Do you want the record to show up in BOTH periods or is one period preferred over the other in some way?

Lisa

 

[kenhamady]

If you only want to return records whose range (from Start to End) INCLUDES the date value of the parameter, you should use a selection formula that like:

{?Date} in {table.Start} to {table.End} Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[BeckyLee]

Ken,
What you are suggesting is approiate but my problem is the that only want 1 record to be return. In the above example if A and B are records and #2 is the {?Date} then I would return two records when I only want record B. So in answering Lisa I feel as though the only way to determine that is the record of choice is because the start date is later and infers that it is the most recent start and end dates.

Becky

 

[kenhamady]

Crystal can't 'return' only one record - it would have to have both in memory to figure out which was later. But if you sort them you can hide the details and only SHOW the latest record. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Becky please clarify something for me. I understand that you have 2 date ranges, and you only want the record to show up in one. What is the rule to determine which range you want this in? Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[BeckyLee]

I used Ken suggestion of surpressing the detail and showing the maxium result in the footer section. That seems to work. Thank you all for helping me. Now that I have the subreports showing correctly I just need to make a calculation in the main report. Are there any Tips on how shared variables work?

Thanks again,
Becky

 

[kenhamady]

They work just like regular CR variables, but when you declare them as &quot;shared&quot; they carry down to the subreport and back. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com