Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Parameter doesn't return data from 2012. Stops at 12/31/2011

Status
Not open for further replies.
SkipWhitten

SkipWhitten

IS-IT--Management
Joined
Jun 2, 2003
Messages
4
Hi,
I have a report that includes a date parameter (equal to ot greater than) that won't return data that has a date in next year (2012). I did not create this but the end users tell me that this happens every December and that the previous consultant would tweak it and they'ld be good for the next 12 months. I would appreciate any guidance and I thank you in advance.



Skip

Skip Whitten
IT Consultant
 
Sort by date Sort by votes
Hi,
Show us the selection formula that uses the parameter and also confirm that the parameter is a Date type.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
The parameter is string. Here is the formula to switch it around.
if length ({tsoSalesOrder.userFld1}) = 10
then {tsoSalesOrder.userFld1}[6 to 7]+ "/" + {tsoSalesOrder.userFld1}[9 to 10] + "/" + {tsoSalesOrder.userFld1}[3 to 4]
else {tsoSalesOrder.userFld1}
The date displays like this after: 12/13/11.

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

Thanks,

Skip

Skip Whitten
IT Consultant
 
Upvote 0 Downvote
I would start with checking the report for hard-coded 2011 or 2012.
The easiest way I know is to export the report to Report Definition file.
Menu: File->Export->Export Report and select Report Definition (TXT). This will export all formulas in a flat text file and will allow you to search easier.

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.
 
Upvote 0 Downvote
Crystal Report Professional v10.0 (32-bit) - Report Definition

1.0 File Information

Report File:
Version: 10.2

2.0 Record Sort Fields
A - {tsoSalesOrder.Expiration}
A - {tsoSalesOrder.UserFld1}

3.0 Group Sort Fields

4.0 Formulas

4.1 Record Selection Formula
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

4.2 Group Selection Formula


4.3 Other Formulas

Name: {@l1}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT1} then {SALES_ORDER_LINE_0.SO1}
else ""

Name: {@l2}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT2} then {SALES_ORDER_LINE_0.SO2}
else ""

Name: {@l4}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT4} then {SALES_ORDER_LINE_0.SO4}
else ""

Name: {@l3}
Formula: if {?Item Types}={SALES_ORDER_LINE_0.IT3} then {SALES_ORDER_LINE_0.SO3}
else ""

Name: {@Sales Order Status}
Formula: if {tsoSalesOrder.Status}= 3 then "CANCELLED" else ""

Name: {@Seed Company}
Formula: If {tarCustClass.CustClassID} like "BRY*" then "BRACHY" else {tarNationalAcct.NationalAcctID}

Name: {@Seed Arrival Date}
Formula: if length ({tsoSalesOrder.userFld1}) = 10
then {tsoSalesOrder.userFld1}[6 to 7]+ "/" + {tsoSalesOrder.userFld1}[9 to 10] + "/" + {tsoSalesOrder.userFld1}[3 to 4]
else {tsoSalesOrder.userFld1}

5.0 Sectional Information

5.1 Page Header Section
Visible, Keep Together

Patient Name
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Company
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Ship To Facility
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Arrive
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Products(s)
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Isotope
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Make Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Ship Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Due to Fac.
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Case Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Total
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



Seed Arrival Report - Sorted by Seed Arrival, Make Date, Ship Date
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



{tsoSalesOrder.CompanyID}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{?Seed Arrival Date}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


Vendor Order #
String, Visible, Left Alignment, Top Alignment,
Keep Together, Expand



5.2 Page Footer Section
Visible, New Page After, Keep Together, Print At Bottom of Page

PageNofM
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


PrintTime
Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Time Base: 12 Hour, AM/PM Type: AM/PM After, Hour Type: Numeric No Leading Zero, Minute Type: Numeric, Second Type: Numeric, AM String: 'AM', PM String: 'PM', Hour Minute Separator: ':', Minute Second Separator: ':'


PrintDate
Date, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Windows Default Type: Use Windows Short Date, Date Order: Month Day Year, Year Type: Long, Month Type: Numeric Month, Day Type: Numeric Day, Leading Day Type: None, First Separator: '/', Second Separator: '/', Leading Day Separator: ''


Print Date:
String, Visible, Left Alignment, Top Alignment,
Keep Together



Filename
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.3 Report Header Section
Hidden, New Page Before

5.4 Report Footer Section
Visible, New Page After

5.5 Group Header Section #1
Visible

GroupName ({@Seed Arrival Date})
String, Visible, Right Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


DistinctCount ({tsoSalesOrder.SOKey}, {@Seed Arrival Date})
Numeric, Visible, Horizontal Centre Alignment, Top Alignment,
Keep Together, Expand,
Bracketed, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


5.6 Group Footer Section #1
Hidden

5.7 Group Header Section #2
Hidden

GroupName ({tsoSalesOrder.Expiration}, "daily")
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.8 Group Footer Section #2
Hidden

5.9 Group Header Section #3
Hidden

GroupName ({tsoSalesOrder.DfltShipDate}, "daily")
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.10 Group Footer Section #3
Hidden

5.11 Group Header Section #4
Hidden

5.12 Group Footer Section #4
Visible

{@l1}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l4}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.Expiration}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltShipDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltPromDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.DfltRequestDate}
Date Time, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Date Time Order: Date Only, Separator: ' '


{tsoSalesOrder.UserFld4}
String, Visible, Horizontal Centre Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.UserFld3}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarCustomer.CustName}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.CustPONo}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarNationalAcct.NationalAcctID}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@l3}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{@Sales Order Status}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tsoSalesOrder.SOKey}
Numeric, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSalesOrder.UserFld1}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


{tarCustClass.CustClassID}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{tarCustClass.CustClassName}
String, Hidden, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{@Seed Company}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{tsoSalesOrder.UserFld2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


GroupName ({tsoSOLine.UserFld2})
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap


{@Seed Arrival Date}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting, Word Wrap


5.13 Group Header Section #5
Hidden

5.14 Group Footer Section #5
Hidden

5.15 Details Section
Hidden

Subsection.1
Visible, Keep Together

{tsoSOLineDist.QtyOrd}
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 2 Decimal Places, Rounding: 0.01,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSOLine.SOLineNo}
Numeric, Visible, Default Alignment, Top Alignment,
Keep Together, Expand, Using System Default Formatting,
Leading Minus, 0 Decimal Places, Rounding: 1,
Thousands Symbol: ',', Decimal Symbol: '.'


{tsoSOLine.UserFld2}
String, Visible, Default Alignment, Top Alignment,
Keep Together, Using System Default Formatting, Word Wrap




Skip Whitten
IT Consultant
 
Upvote 0 Downvote
I guess you already tried to change DateTime (2011, 12, 01, 00, 00, 00) to DateTime (2012, 12, 01, 00, 00, 00)
in the select formula:

not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2012, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2012, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2012, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.
 
Upvote 0 Downvote
To Debug I would remove one line of your select and rerun report repeat until correct data for 2012 arrives eg

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.Expiration} >= DateTime (2011, 12, 01, 00, 00, 00)

then

Here is the 'Select'
not ({tsoSalesOrder.Status} in [4]) and
{tsoSalesOrder.CompanyID} = "BIO" and
{tsoSalesOrder.DfltPromDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{tsoSalesOrder.DfltShipDate} >= DateTime (2011, 12, 01, 00, 00, 00) and
{@Seed Arrival Date} >= {?Seed Arrival Date}

etc

Also place filter fields details of report and see what is being returned.

Ian
 
Upvote 0 Downvote
You might want to replace the 2011 in the selection formula with a number parameter {?Year}. If your fiscal year starts 12/1 and the current fiscal year is FY 2012, you might want to substitute {?FiscalYear}-1 for 2011.

Also, although it is not the basis of your problem, I would require users to enter the full year to avoid confusion of year and month and also standardize the entry to yyyy/MM/dd. It looks like you are also allowing different lengths. I'm wondering what the content of that field looks like before you try to standardize it by formula.

-LB


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JustMe24
  • Locked
  • Question Question
How to select from 2 rows in a table
Replies
1
Views
706
SoftwareRT
SoftwareRT
D
  • Locked
  • Question Question
Stored Procedure with no Parameters
Replies
1
Views
754
hilfy
hilfy
RoknRole
  • Locked
  • Question Question
Static parameter list of values limitation
Replies
1
Views
549
RoknRole
RoknRole
PeteyN.O
  • Locked
  • Question Question
Crystal Report passing a date parameter to a number type field
Replies
3
Views
433
hilfy
hilfy
Niki_S
  • Locked
  • Question Question
Error when creating parameter fields
Replies
4
Views
589
kray4660
kray4660

Part and Inventory Search

Sponsor

Back
Top