Help with field in a report

[Former Member]

I have these fields:

'Survey date' - entered as 2/8/2017

'Next Survey date' - entered as 2/8/2018

'Follow Up date'- (This field is a text field and is approximately 30 days from the survey date. Noted as ‘FolUp 3/8/17’)

I am trying to create a report that displays as follows:

JANUARY

Company Name Acct No. Survey date Next survey date Follow Up date

FEBRUARY

Company Name Acct No. Survey date Next survey date Follow Up date

MARCH

Company Name Acct No. Survey date Next survey date Follow Up date

The problem I am having is with the 'Follow up date' field.It’s pulling in the 3/8/17 date under the FEBRUARY category since that is the date currently in that field.

Example:

FEBRUARY

Company Name Acct No. Survey date Next survey date Follow up date

ABC Inc. 12345 2/8/17 2/8/18 FolUp 3/8/17

I want the Follow up date field to show all February entries under FEBRUARY and not March entries

How do I do this?

Thanks in advance for your help.

[DellSC · Accepted solution]

The problem with what you're trying to do is that the Follow Up Date is a string, which won't sort correctly date-wise. so, you need to convert it to a date. A formula to do that might look like this (this assumes that the field is ALWAYS formatted with "FolUp" and that the date is correctly formatted after that...):

StringVar dateStr := Trim(Replace(UpperCase({table.FollowUpDate}), 'FOLUP', ''));

if IsDate(dateStr) then
CDate(dateStr)

This will return the date from the field or a null or the default value if there is no date in the field (value will depend on whether you have "Default values for nulls" turned on.)

You would then group by this formula instead of the field.

Note: If you have only one record for each company, you can't have it show up in both the month for the survey and next survey dates AND the month for the follow up date. You have to select one or the other for sorting/grouping the data.

-Dell

[Former Member]

I’m still having a hard time trying to get the report to display the fields like I want to.

This is the data:

Field:Company Name

Field: Account No.

Field: Survey Date (Date Field) – (example 2/15/17)

Field: Next Survey Date (Date Field)--(example 2/15/18)

Follow Up date (Text field converted to a date field format) – (3/15/17) *this date will be 30 days ahead of the ‘Survey Date’ field.

On the report:

I am trying to list the info. in the following format:

JANUARY

Company Name + Account No.

Survey Date (

Next Survey Date

Follow Up Date

FEBRUARY

Company Name + Account No.

Survey Date

Next Survey Date

Follow Up Date

Etc….

The problem I’m having is with the Follow Up Date – It’s pulling in the 30 day ahead date, which is 2/15/17 .Is there a way to make it display only January dates for the 3 date fields mentioned above?

Basically I’m trying to capture – in the month of Jan – how many Survey Done, Next survey Due and how many Follow Ups due.

Sorry for the lengthy post.New to Crystal reports, and don’t know how else to describe what I’m trying to do.Thank you for your Help.

[Former Member]

Thank you. That made sorting easier. I'm still having a challenge grouping/categorizing the 3 fields according to months. It still pulls in the 30 day ahead date for the current month. Not being very familiar with Crystal Reports, I will work with the report some more and see if I can get it to display the way I need it to.

Thanks again for your help.

[Former Member]

Thanks for your response.

All fields are in the same table.

I have tried grouping on each of the three date fields but the 'follow-up' date still shows as 30 days ahead. Same if I remove the group. The 30 days ahead date still shows while the other 2 date fields pull in the current month's date.

,

Thank you for your response.

All 3 fields are in the same table.

I've tried group by each of the 3 fields, but the 'follow up' date still displays the 30 days ahead date.

If the remove the group, same thing as above - the follow up date still shows the 30 days ahead while other fields display current month dates.

Thank You.

[abhilash_kumar]

Hi Gina,

Is the 'Follow-up Date' in another table?

Which of these date fields have you grouped the report on?

Also, if you remove the group, does the 'follow-up' date appear correctly for each Account Number along with other dates?

-Abhilash