Partial Date
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17994
Printed Date: 01 May 2024 at 6:49pm
Topic: Partial Date
Posted By: tucker2m
Subject: Partial Date
Date Posted: 12 Nov 2012 at 8:37am
|
I have a date field that is pulling from my database as "MM/DD/YYYY". I have converted the date field to a text date range and have no problems with the dates appearing except when the day field is not populated. So in the database, it may appear as "11//2012". I have tried conditional formatting to no avail. I am trying to figure out a custom style in the Format Editor to see if the day is blank/null to replace the day with the text "TBD" but only partially works. I have tried conditional formatting, but does not work since I have already converted the date to text.
I tried the following:
If Day({eloans_csv.InfVenueStartDate0}) < 1 Then
"TBD" Else "" But get the error that a date is required in the field. I also tried doing string if the day is null, then added the text, but that would not work. Anyone else encounter such issues with dates? Thanks.
|
Replies:
Posted By: hilfy
Date Posted: 12 Nov 2012 at 10:57am
|
Try something like this: If Instr({eloans_csv.InfVenueStartDate0}, '\\') > 0 then "TBD" else {eloans_csv.InfVenueStartDate0} -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: tucker2m
Date Posted: 13 Nov 2012 at 5:23am
|
Hi Dell, thanks, I tried that but I suspect I am trying this in the wrong place. I have the dates in a subreport. I then added the dates to a text box and changed the format of the dates from YYYY/DD/MM to "January 1, 2012" format. The dates are linked via two keys in the subreport in order to link (the dates correctly to the venue (see below). When I then add run the report, everything is linked properly and all dates appear correctly except if the date appears as "2013-05-" due to a partial date being entered in the database (instead of appearing as 2013/05/20". If the later is the date that is pulled from the database, no issues. If it is the former, then the date does not appear on the final report. I changed the field in the database for the value to appear as text (per suggestion from database people), but that only repeated the information multiple times and the information wouldn't appear. So went back to original format and trying to figure out in the design phase how to read the dates if crystal sees the dates as "2014-05-" to actually pull the information and change it to "May 2014". In your experience, where would you insert such a string? Thanks. -Maureen |
Posted By: hilfy
Date Posted: 13 Nov 2012 at 5:46am
|
I'm seeing you mention dates in a number of formats. What, exactly, is the data type of the field that contains the date? Also, please provide sample data fo ALL of the date formats that are shown in the database. Thanks! -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: comatt1
Date Posted: 13 Nov 2012 at 5:49am
|
you can evaluate for len, if no date is there,
if len(date)<> 10 then add the TBD :) |
Posted By: tucker2m
Date Posted: 13 Nov 2012 at 8:06am
| Where would you put a "len" formula? I have added the fields directly into a text box and they are in a subreport. The fields themselves were converted to text using the Format Editor->Date and Time style. |
Posted By: tucker2m
Date Posted: 13 Nov 2012 at 8:22am
|
Hi there, the dates are date type tables. In the db itself, they can appear as below: When adding the field to the report on the db side, I change the table to read as text upon exporting in the CSV files. So in the CSV file they appear as two different tables with the data appearing as below.
So the issue is that in crystal, the data is not pulling for the first because it does not recognize that as a date because in crystal, I have changed the format for the field from a MM/DD/YYYY date/time stamp to appear as "January 01, 2012" text format. For some reason crystal just suppresses the first incomplete dates so when I run the preview, the field is empty for the corresponding record. If the date has the correct date format, the full dates appear in the report. So it is definitely on the crystal side that is is suppressing. Here is my linking for the fields: Since I am getting the information from the CSV files, it is definitely something in crystal with how it is reading the data. Ugh, this is frustrating. |
Posted By: comatt1
Date Posted: 13 Nov 2012 at 8:24am
|
take the field and add it into a formula, and display the formula not the text field (suppress it)
you can run len against a formula that returns type string just read what you said about your bad data.. you will have to validate: if isdate(field) then run logic. Otherwise you need to be creative, I would import all of if as a string. |
Posted By: tucker2m
Date Posted: 13 Nov 2012 at 8:25am
oops, images didn't make it over.
|
Posted By: comatt1
Date Posted: 13 Nov 2012 at 8:28am
|
ex. 05//2013
if not(isdate(above value)) then replace(value, '//', '/TBD/') |
Posted By: tucker2m
Date Posted: 13 Nov 2012 at 8:28am
|
So my typical formula works partially. Local StringVar Dates := ''; if Not IsNull({eloans_csv.InfVenueStartDate0}) or Not IsNull({eloans_csv.InfVenueEndDate0}) then ( if Not IsNull({eloans_csv.InfVenueStartDate0}) then Dates := {eloans_csv.InfVenueStartDate0}; if Not IsNull({eloans_csv.InfVenueEndDate0}) then ( if Dates <> '' then Dates := Dates + " - " + {eloans_csv.InfVenueEndDate0} else Dates := {eloans_csv.InfVenueEndDate0}; ) ) It repeats the information for each venue then. |